CREATE TABLE FileGroupField
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Name VARCHAR(200) NOT NULL UNIQUE,
    Description VARCHAR(MAX) NULL
)
GO

CREATE TABLE FileGroupFieldValue
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    FileGroupID INT NOT NULL REFERENCES FileGroup(ID),
    FileGroupFieldID INT NOT NULL REFERENCES FileGroupField(ID),
    Value VARCHAR(MAX) NULL
)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupFieldValue_FileGroupID
ON FileGroupFieldValue(FileGroupID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupFieldValue_FileGroupFieldID
ON FileGroupFieldValue(FileGroupFieldID ASC)
GO

ALTER TABLE Line
ADD MaxFaultDistance FLOAT NULL
GO

ALTER TABLE Line
ADD MinFaultDistance FLOAT NULL
GO

CREATE NONCLUSTERED INDEX IX_Channel_MeterID_MeasurementTypeID_MeasurementCharacteristicID_PhaseID_HarmonicGroup
ON Channel(MeterID ASC, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup)
GO

CREATE TABLE AssetGroup
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Name VARCHAR(200) NOT NULL
)
GO

EXEC sp_rename 'DeviceFilter', 'DeviceFilter_org'
GO

CREATE TABLE DeviceFilter
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    UserAccount VARCHAR(500) NOT NULL,
    Name NVARCHAR(500) NOT NULL,
    FilterExpression NVARCHAR(MAX) NOT NULL,
    AssetGroupID INT NOT NULL
)
GO

INSERT INTO DeviceFilter
SELECT UserAccount, DeviceFilter_org.Name, FilterExpression, AssetGroup.ID
FROM
    DeviceFilter_org JOIN
    MeterGroup ON DeviceFilter_org.MeterGroupID = MeterGroup.ID JOIN
    AssetGroup ON
        MeterGroup.Name = AssetGroup.Name OR
        (MeterGroup.Name = 'AllMeters' AND AssetGroup.Name = 'AllAssets')
GO

CREATE TABLE MeterAssetGroup
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    MeterID INT NOT NULL REFERENCES Meter(ID),
    AssetGroupID INT NOT NULL REFERENCES AssetGroup(ID)
)
GO

CREATE NONCLUSTERED INDEX IX_MeterAssetGroup_MeterID
ON MeterAssetGroup(MeterID ASC)
GO

CREATE NONCLUSTERED INDEX IX_MeterAssetGroup_AssetGroupID
ON MeterAssetGroup(AssetGroupID ASC)
GO

CREATE TRIGGER Meter_AugmentAllAssetsGroup
ON Meter
AFTER INSERT
AS BEGIN
    SET NOCOUNT ON;

    INSERT INTO MeterAssetGroup(MeterID, AssetGroupID)
    SELECT Meter.ID, AssetGroup.ID
    FROM inserted Meter CROSS JOIN AssetGroup
    WHERE AssetGroup.Name = 'AllAssets'
END
GO

CREATE TABLE LineAssetGroup
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    LineID INT NOT NULL REFERENCES Line(ID),
    AssetGroupID INT NOT NULL REFERENCES AssetGroup(ID),
)
GO

CREATE NONCLUSTERED INDEX IX_LineAssetGroup_LineID
ON LineAssetGroup(LineID ASC)
GO

CREATE NONCLUSTERED INDEX IX_LineAssetGroup_AssetGroupID
ON LineAssetGroup(AssetGroupID ASC)
GO

CREATE TRIGGER Line_AugmentAllAssetsGroup
ON Line
AFTER INSERT
AS BEGIN
    SET NOCOUNT ON;

    INSERT INTO LineAssetGroup(LineID, AssetGroupID)
    SELECT Line.ID, AssetGroup.ID
    FROM inserted Line CROSS JOIN AssetGroup
    WHERE AssetGroup.Name = 'AllAssets'
END
GO

CREATE TABLE AssetGroupAssetGroup
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    ParentAssetGroupID INT NOT NULL REFERENCES AssetGroup(ID),
    ChildAssetGroupID INT NOT NULL REFERENCES AssetGroup(ID),
)
GO

CREATE NONCLUSTERED INDEX IX_AssetGroupAssetGroup_ParentAssetGroupID
ON AssetGroupAssetGroup(ParentAssetGroupID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AssetGroupAssetGroup_ChildAssetGroupID
ON AssetGroupAssetGroup(ChildAssetGroupID ASC)
GO

CREATE TABLE MaintenanceWindow
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    MeterID INT NOT NULL REFERENCES Meter(ID),
    StartTime DATETIME,
    EndTime DATETIME
)
GO

INSERT INTO AssetGroup(Name) VALUES('AllAssets')
GO

INSERT INTO AssetGroup(Name)
SELECT Name
FROM MeterGroup
WHERE Name <> 'AllMeters'
GO

INSERT INTO MeterAssetGroup
SELECT MeterMeterGroup.MeterID, AssetGroup.ID
FROM
    MeterMeterGroup JOIN
    MeterGroup ON MeterMeterGroup.MeterGroupID = MeterGroup.ID JOIN
    AssetGroup ON
        MeterGroup.Name = AssetGroup.Name OR
        (MeterGroup.Name = 'AllMeters' AND AssetGroup.Name = 'AllAssets')
GO

ALTER TABLE UserAccount
ADD CONSTRAINT UQ_UserAccount_Name UNIQUE(Name)
GO

ALTER TABLE UserAccount
ADD PhoneConfirmed BIT NOT NULL DEFAULT 0
GO

ALTER TABLE UserAccount
ADD EmailConfirmed BIT NOT NULL DEFAULT 0
GO

ALTER TABLE UserAccount
ADD Approved BIT NOT NULL DEFAULT 0
GO

UPDATE UserAccount
SET
    EmailConfirmed = 1,
    Approved = 1
GO

CREATE TABLE UserAccountAssetGroup
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    UserAccountID UNIQUEIDENTIFIER NOT NULL REFERENCES UserAccount(ID),
    AssetGroupID INT NOT NULL REFERENCES AssetGroup(ID),
    Dashboard BIT NOT NULL DEFAULT 1,
    Email BIT NOT NULL DEFAULT 0
)
GO

INSERT INTO UserAccountAssetGroup
SELECT UserAccountMeterGroup.UserAccountID, AssetGroup.ID, 1, 1
FROM
    UserAccountMeterGroup JOIN
    MeterGroup ON UserAccountMeterGroup.MeterGroupID = MeterGroup.ID JOIN
    AssetGroup ON
        MeterGroup.Name = AssetGroup.Name OR
        (MeterGroup.Name = 'AllMeters' AND AssetGroup.Name = 'AllAssets')
GO

CREATE TRIGGER UserAccount_AugmentAllAssetsGroup
ON UserAccount
AFTER INSERT
AS BEGIN
    SET NOCOUNT ON;

    INSERT INTO UserAccountAssetGroup(UserAccountID, AssetGroupID)
    SELECT UserAccount.ID, AssetGroup.ID
    FROM inserted UserAccount CROSS JOIN AssetGroup
    WHERE AssetGroup.Name = 'AllAssets'
END
GO

ALTER TABLE XSLTemplate
ADD CONSTRAINT UQ_XSLTemplate_Name UNIQUE(Name)
GO

ALTER TABLE EmailType
ADD SMS BIT NOT NULL DEFAULT 0
GO

CREATE TABLE EventEmailParameters
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    EmailTypeID INT NOT NULL UNIQUE REFERENCES EmailType(ID),
    TriggersEmailSQL VARCHAR(MAX) NOT NULL DEFAULT 'SELECT 0',
    EventDetailSQL VARCHAR(MAX) NOT NULL DEFAULT 'SELECT '''' FOR XML PATH(''EventDetail''), TYPE',
    MinDelay FLOAT NOT NULL DEFAULT 10,
    MaxDelay FLOAT NOT NULL DEFAULT 60
)
GO

CREATE TABLE UserAccountEmailType
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    UserAccountID UNIQUEIDENTIFIER NOT NULL REFERENCES UserAccount(ID),
    EmailTypeID INT NOT NULL REFERENCES EmailType(ID)
)
GO

INSERT INTO UserAccountEmailType
SELECT EmailGroupUserAccount.UserAccountID, EmailGroupType.EmailTypeID
FROM
    EmailGroupUserAccount JOIN
    EmailGroupType ON EmailGroupUserAccount.EmailGroupID = EmailGroupType.EmailGroupID
GO

INSERT INTO EventEmailParameters(EmailTypeID)
SELECT EmailType.ID
FROM
    EmailType JOIN
    EmailCategory ON EmailType.EmailCategoryID = EmailCategory.ID
WHERE EmailCategory.Name = 'Event'
GO

UPDATE EventEmailParameters
SET TriggersEmailSQL = 'SELECT
    CASE WHEN EventType.Name = ''Fault''
        THEN 1
        ELSE 0
    END
FROM
    Event JOIN
    EventType ON Event.EventTypeID = EventType.ID
WHERE Event.ID = {0}'
WHERE EmailTypeID = (SELECT ID FROM EmailType WHERE XSLTemplateID = (SELECT ID FROM XSLTemplate WHERE Name = 'Default Fault'))
GO

UPDATE EventEmailParameters
SET EventDetailSQL = 'DECLARE @timeTolerance FLOAT = (SELECT CAST(Value AS FLOAT) FROM Setting WHERE Name = ''TimeTolerance'')
DECLARE @lineID INT
DECLARE @startTime DATETIME2
DECLARE @endTime DATETIME2

SELECT
    @lineID = LineID,
    @startTime = dbo.AdjustDateTime2(StartTime, -@timeTolerance),
    @endTime = dbo.AdjustDateTime2(EndTime, @timeTolerance)
FROM Event
WHERE ID = {0}

SELECT *
INTO #lineEvent
FROM Event
WHERE
    Event.LineID = @lineID AND
    Event.EndTime >= @startTime AND
    Event.StartTime <= @endTime

SELECT
    ROW_NUMBER() OVER(PARTITION BY Event.MeterID ORDER BY FaultSummary.Inception) AS FaultNumber,
    FaultSummary.ID AS FaultSummaryID,
    Meter.AssetKey AS MeterKey,
    Meter.Name AS MeterName,
    MeterLocation.AssetKey as StationKey,
    MeterLocation.Name AS StationName,
    Line.AssetKey AS LineKey,
    MeterLine.LineName,
    FaultSummary.FaultType,
    FaultSummary.Inception,
    FaultSummary.DurationCycles,
    FaultSummary.DurationSeconds * 1000.0 AS DurationMilliseconds,
    CASE WHEN FaultSummary.PrefaultCurrent <> -1E308 THEN FORMAT(FaultSummary.PrefaultCurrent, ''0.##########'') ELSE ''NaN'' END AS PrefaultCurrent,
    CASE WHEN FaultSummary.PostfaultCurrent <> -1E308 THEN FORMAT(FaultSummary.PostfaultCurrent, ''0.##########'') ELSE ''NaN'' END AS PostfaultCurrent,
    FaultSummary.ReactanceRatio,
    FaultSummary.CurrentMagnitude AS FaultCurrent,
    FaultSummary.Algorithm,
    FaultSummary.Distance AS SingleEndedDistance,
    DoubleEndedFaultSummary.Distance AS DoubleEndedDistance,
    DoubleEndedFaultSummary.Angle AS DoubleEndedAngle,
    RIGHT(DataFile.FilePath, CHARINDEX(CHAR(92), REVERSE(DataFile.FilePath)) - 1) AS FileName,
    FaultSummary.EventID,
    Event.StartTime AS EventStartTime,
    Event.EndTime AS EventEndTime
INTO #summaryData
FROM
    #lineEvent Event JOIN
    EventType ON
        Event.EventTypeID = EventType.ID AND
        EventType.Name = ''Fault'' JOIN
    FaultSummary ON
        FaultSummary.EventID = Event.ID AND
        FaultSummary.IsSelectedAlgorithm <> 0 JOIN
    DataFile ON DataFile.FileGroupID = Event.FileGroupID JOIN
    Meter ON Event.MeterID = Meter.ID JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
    MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Event.LineID JOIN
    Line ON MeterLine.LineID = Line.ID LEFT OUTER JOIN
    DoubleEndedFaultDistance ON DoubleEndedFaultDistance.LocalFaultSummaryID = FaultSummary.ID LEFT OUTER JOIN
    DoubleEndedFaultSummary ON DoubleEndedFaultSummary.ID = DoubleEndedFaultDistance.ID
WHERE
    DataFile.FilePath LIKE ''%.DAT'' OR
    DataFile.FilePath LIKE ''%.D00'' OR
    DataFile.FilePath LIKE ''%.PQD'' OR
    DataFile.FilePath LIKE ''%.RCD'' OR
    DataFile.FilePath LIKE ''%.RCL'' OR
    DataFile.FilePath LIKE ''%.SEL'' OR
    DataFile.FilePath LIKE ''%.EVE'' OR
    DataFile.FilePath LIKE ''%.CEV''

DECLARE @url VARCHAR(MAX) = (SELECT Value FROM DashSettings WHERE Name = ''System.URL'')

SELECT
    (
        SELECT ID AS [@id]
        FROM #lineEvent
        FOR XML PATH(''Event''), TYPE
    ) AS [Events],
    (
        SELECT
            FaultNumber AS [@num],
            (
                SELECT
                    MeterKey,
                    MeterName,
                    StationKey,
                    StationName,
                    LineKey,
                    LineName,
                    FaultType,
                    Inception,
                    DurationCycles,
                    DurationMilliseconds,
                    PrefaultCurrent,
                    PostfaultCurrent,
                    ReactanceRatio,
                    FaultCurrent,
                    Algorithm,
                    SingleEndedDistance,
                    DoubleEndedDistance,
                    DoubleEndedAngle,
                    EventStartTime,
                    EventEndTime,
                    FileName,
                    EventID,
                    FaultSummaryID AS FaultID
                FROM #summaryData
                WHERE FaultNumber = Fault.FaultNumber
                FOR XML PATH(''SummaryData''), TYPE
            )
        FROM
        (
            SELECT DISTINCT FaultNumber
            FROM #summaryData
        ) Fault
        FOR XML PATH(''Fault''), TYPE
    ) AS [Faults],
    MeterLine.LineName AS [Line/Name],
    Line.AssetKey AS [Line/AssetKey],
    FORMAT(Line.Length, ''0.##########'') AS [Line/Length],
    FORMAT(SQRT(LineImpedance.R1 * LineImpedance.R1 + LineImpedance.X1 * LineImpedance.X1), ''0.##########'') AS [Line/Z1],
    CASE LineImpedance.R1 WHEN 0 THEN ''0'' ELSE FORMAT(ATN2(LineImpedance.X1, LineImpedance.R1) * 180 / PI(), ''0.##########'') END AS [Line/A1],
    FORMAT(LineImpedance.R1, ''0.##########'') AS [Line/R1],
    FORMAT(LineImpedance.X1, ''0.##########'') AS [Line/X1],
    FORMAT(SQRT(LineImpedance.R0 * LineImpedance.R0 + LineImpedance.X0 * LineImpedance.X0), ''0.##########'') AS [Line/Z0],
    CASE LineImpedance.R0 WHEN 0 THEN ''0'' ELSE FORMAT(ATN2(LineImpedance.X0, LineImpedance.R0) * 180 / PI(), ''0.##########'') END AS [Line/A0],
    FORMAT(LineImpedance.R0, ''0.##########'') AS [Line/R0],
    FORMAT(LineImpedance.X0, ''0.##########'') AS [Line/X0],
    FORMAT(SQRT(POWER((2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0, 2) + POWER((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, 2)), ''0.##########'') AS [Line/ZS],
    CASE 2.0 * LineImpedance.R1 + LineImpedance.R0 WHEN 0 THEN ''0'' ELSE FORMAT(ATN2((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, (2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0) * 180 / PI(), ''0.##########'') END AS [Line/AS],
    FORMAT((2.0 * LineImpedance.R1 + LineImpedance.R0) / 3.0, ''0.##########'') AS [Line/RS],
    FORMAT((2.0 * LineImpedance.X1 + LineImpedance.X0) / 3.0, ''0.##########'') AS [Line/XS],
    @url AS [PQDashboard]
FROM
    Event JOIN
    Line ON Event.LineID = Line.ID JOIN
    MeterLine ON
        MeterLine.MeterID = Event.MeterID AND
        MeterLine.LineID = Event.LineID JOIN
    LineImpedance ON LineImpedance.LineID = Line.ID
WHERE Event.ID = {0}
FOR XML PATH(''EventDetail''), TYPE'
WHERE EmailTypeID = (SELECT ID FROM EmailType WHERE XSLTemplateID = (SELECT ID FROM XSLTemplate WHERE Name = 'Default Fault'))
GO

DELETE FROM DataOperation
WHERE TypeName = 'FaultData.DataWriters.EventEmailWriter'
GO

CREATE TABLE SnapshotHarmonics
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    EventID INT NOT NULL REFERENCES Event(ID),
    ChannelID INT NOT NULL REFERENCES Channel(ID),
    SpectralData varchar(max) NULL
)
GO

CREATE NONCLUSTERED INDEX IX_SnapshotHarmonics_EventID
ON SnapshotHarmonics(EventID ASC)
GO

INSERT INTO EventType(Name, Description) VALUES ('BreakerOpen', 'Breaker Opening - Nonfault')
GO

INSERT INTO EventType(Name, Description) VALUES ('Snapshot', 'Snapshot')
GO

CREATE TABLE FaultCauseMetrics
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    EventID INT NOT NULL REFERENCES Event(ID),
    FaultNumber INT NOT NULL,
    TreeFaultResistance FLOAT NULL,
    LightningMilliseconds FLOAT NULL,
    InceptionDistanceFromPeak FLOAT NULL,
    PrefaultThirdHarmonic FLOAT NULL,
    GroundCurrentRatio FLOAT NULL,
    LowPrefaultCurrentRatio FLOAT NULL
)
GO

CREATE NONCLUSTERED INDEX IX_FaultCauseMetrics_EventID
ON FaultCauseMetrics(EventID ASC)
GO

CREATE TABLE Unit
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name VARCHAR(MAX) NOT NULL
)
GO

CREATE TABLE PQMeasurement
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name VARCHAR(MAX) NOT NULL,
    Description VARCHAR(MAX) NULL,
    UnitID INT NOT NULL REFERENCES Unit(ID),
    MeasurementTypeID INT NOT NULL REFERENCES MeasurementType(ID),
    MeasurementCharacteristicID INT NOT NULL REFERENCES MeasurementCharacteristic(ID),
    PhaseID INT NOT NULL REFERENCES Phase(ID),
    HarmonicGroup INT NOT NULL DEFAULT 0,
    Enabled bit NOT NULL DEFAULT 1
)
GO

CREATE TABLE PQTrendStat
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    MeterID INT NOT NULL REFERENCES Meter(ID),
    PQMeasurementTypeID INT NOT NULL REFERENCES PQMeasurement(ID),
    Date DATE NOT NULL,
    Max FLOAT NULL,
    CP99 FLOAT NULL,
    CP95 FLOAT NULL,
    Avg FLOAT NULL,
    CP05 FLOAT NULL,
    CP01 FLOAT NULL,
    Min FLOAT NULL
)
GO

CREATE NONCLUSTERED INDEX IX_PQTrendStat_Date
ON PQTrendStat(Date ASC)
GO

CREATE NONCLUSTERED INDEX IX_PQTrendStat_MeterID_Date
ON PQTrendStat(MeterID ASC, Date ASC)
GO

CREATE NONCLUSTERED INDEX IX_PQTrendStat_MeterID_PQMeasurementTypeID_Date
ON PQTrendStat(Date DESC, MeterID ASC, PQMeasurementTypeID ASC)
GO

INSERT INTO Unit (Name) VALUES ('Volts')
GO

INSERT INTO Unit (Name) VALUES ('Amps')
GO

INSERT INTO Unit (Name) VALUES ('KW')
GO

INSERT INTO Unit (Name) VALUES ('KVAR')
GO

INSERT INTO Unit (Name) VALUES ('KVA')
GO

INSERT INTO Unit (Name) VALUES ('Per Unit')
GO

INSERT INTO Unit (Name) VALUES ('Percent')
GO

CREATE TABLE StepChangeMeasurement(
    ID INT PRIMARY KEY IDENTITY(1,1),
    PQMeasurementID INT FOREIGN KEY REFERENCES PQMeasurement(ID) NOT NULL,
    Setting FLOAT NULL
)
GO

CREATE NONCLUSTERED INDEX IX_StepChangeMeasurement_PQMeasurement
ON StepChangeMeasurement(PQMeasurementID ASC)
GO

CREATE TABLE StepChangeStat(
    ID INT PRIMARY KEY IDENTITY(1,1),
    MeterID INT FOREIGN KEY REFERENCES Meter(ID) NOT NULL,
    Date Date NOT NULL,
    StepChangeMeasurementID INT FOREIGN KEY REFERENCES StepChangeMeasurement(ID) NOT NULL,
    Value FLOAT NULL
)
GO

CREATE NONCLUSTERED INDEX IX_StepChangeStat_Date
ON StepChangeStat(Date ASC)
GO

CREATE NONCLUSTERED INDEX IX_StepChangeStat_MeterID_Date
ON StepChangeStat(MeterID ASC, Date ASC)
GO

CREATE NONCLUSTERED INDEX IX_StepChangeStat_MeterID_StepChangeMeasurementID_Date
ON StepChangeStat(MeterID ASC,StepChangeMeasurementID ASC, Date ASC)
GO

CREATE TABLE Report
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    MeterID INT NOT NULL REFERENCES Meter(ID),
    Month INT NOT NULL,
    Year INT NOT NULL,
    Results VARCHAR(4) NOT NULL,
    PDF VARBINARY(MAX) NOT NULL,
    CONSTRAINT UC_Report UNIQUE(ID, MeterID, Month, Year)
)
GO

CREATE TABLE EventStat
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    EventID INT NOT NULL REFERENCES Event(ID),
    VPeak FLOAT NULL,
    VAMax FLOAT NULL,
    VBMax FLOAT NULL,
    VCMax FLOAT NULL,
    VABMax FLOAT NULL,
    VBCMax FLOAT NULL,
    VCAMax FLOAT NULL,
    VAMin FLOAT NULL,
    VBMin FLOAT NULL,
    VCMin FLOAT NULL,
    VABMin FLOAT NULL,
    VBCMin FLOAT NULL,
    VCAMin FLOAT NULL,
    IPeak FLOAT NULL,
    IAMax FLOAT NULL,
    IBMax FLOAT NULL,
    ICMax FLOAT NULL,
    IA2t FLOAT NULL,
    IB2t FLOAT NULL,
    IC2t FLOAT NULL,
    InitialMW FLOAT NULL,
    FinalMW FLOAT NULL,
    PQViewID INT NULL,
    CONSTRAINT UC_EventStat_EventID UNIQUE(EventID)
)
GO

ALTER FUNCTION ComputeHash
(
    @eventID INT,
    @templateID INT
)
RETURNS BIGINT
BEGIN
    DECLARE @md5Hash BINARY(16)

    DECLARE @eventDetailSQL VARCHAR(MAX) =
    (
        SELECT EventDetailSQL
        FROM
            EmailType JOIN
            EventEmailParameters ON EventEmailParameters.EmailTypeID = EmailType.ID
        WHERE EmailType.XSLTemplateID = @templateID
    )

    DECLARE @eventDetail VARCHAR(MAX)
    EXEC sp_executesql @eventDetailSQL, @eventID, @eventDetail OUT

    SELECT @md5Hash = master.sys.fn_repl_hash_binary(CONVERT(VARBINARY(MAX), @eventDetail))
    FROM EventDetail
    WHERE EventID = @eventID

    SELECT @md5Hash = master.sys.fn_repl_hash_binary(@md5Hash + CONVERT(VARBINARY(MAX), Template))
    FROM XSLTemplate
    WHERE ID = @templateID

    RETURN CONVERT(BIGINT, SUBSTRING(@md5Hash, 0, 8)) ^ CONVERT(BIGINT, SUBSTRING(@md5Hash, 8, 8))
END
GO

CREATE FUNCTION RecursiveMeterSearch(@assetGroupID int)
RETURNS TABLE
AS
RETURN
    WITH AssetGroupHeirarchy AS
    (
        SELECT ParentAssetGroupID, ChildAssetGroupID
        FROM AssetGroupAssetGroup
        WHERE ParentAssetGroupID = @assetGroupID  -- anchor member
        UNION ALL
        SELECT b.ParentAssetGroupID, a.ChildAssetGroupID -- recursive member
        FROM
            AssetGroupAssetGroup AS a JOIN
            AssetGroupHeirarchy AS b ON b.ChildAssetGroupID = a.ParentAssetGroupID
    )
    SELECT DISTINCT MeterID AS ID
    FROM
        MeterAssetGroup LEFT JOIN
        AssetGroupHeirarchy ON MeterAssetGroup.AssetGroupID = AssetGroupHeirarchy.ChildAssetGroupID
    WHERE
        MeterAssetGroup.AssetGroupID = @assetGroupID OR
        MeterAssetGroup.AssetGroupID IN (SELECT ChildAssetGroupID FROM AssetGroupHeirarchy)
GO

ALTER VIEW MeterLineDetail
AS
SELECT
    MeterLine.ID,
    MeterLine.MeterID,
    Meter.AssetKey AS MeterKey,
    Meter.Name AS MeterName,
    MeterLine.LineID,
    Line.AssetKey AS LineKey,
    MeterLine.LineName,
    FaultDetectionLogic.Expression as FaultDetectionLogic
FROM
    MeterLine JOIN
    Meter ON MeterLine.MeterID = Meter.ID JOIN
    Line ON MeterLIne.LineID = Line.ID LEFT JOIN
    FaultDetectionLogic ON FaultDetectionLogic.MeterLineID = MeterLine.ID
GO

CREATE VIEW AssetGroupAssetGroupView
AS
SELECT
    AssetGroupAssetGroup.ID,
    AssetGroupAssetGroup.ParentAssetGroupID,
    AssetGroupAssetGroup.ChildAssetGroupID,
    Parent.Name as ParentAssetGroupName,
    Child.Name as ChildAssetGroupName
FROM
    AssetGroupAssetGroup JOIN
    AssetGroup as Parent ON AssetGroupAssetGroup.ParentAssetGroupID = Parent.ID JOIN
    AssetGroup as Child ON AssetGroupAssetGroup.ChildAssetGroupID = Child.ID
GO

CREATE VIEW MeterAssetGroupView
AS
SELECT
    MeterAssetGroup.ID,
    Meter.Name AS MeterName,
    Meter.ID AS MeterID,
    AssetGroupID,
    MeterLocation.Name AS Location
FROM
    MeterAssetGroup JOIN
    Meter ON MeterAssetGroup.MeterID = Meter.ID JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID
GO

CREATE VIEW LineAssetGroupView
AS
SELECT
    LineAssetGroup.ID,
    Line.AssetKey AS LineName,
    (SELECT TOP 1 LineName FROM MeterLine Where LineID = Line.ID) AS LongLineName,
    Line.ID AS LineID,
    AssetGroupID
FROM
    LineAssetGroup JOIN
    Line ON LineAssetGroup.LineID = Line.ID
GO

CREATE VIEW UserAccountAssetGroupView
AS
SELECT
    UserAccountAssetGroup.ID,
    UserAccountAssetGroup.UserAccountID,
    UserAccountAssetGroup.AssetGroupID,
    UserAccountAssetGroup.Dashboard,
    UserAccountAssetGroup.Email,
    UserAccount.Name AS Username,
    AssetGroup.Name AS GroupName
FROM
    UserAccountAssetGroup JOIN
    UserAccount ON UserAccountAssetGroup.UserAccountID = UserAccount.ID JOIN
    AssetGroup ON UserAccountAssetGroup.AssetGroupID = AssetGroup.ID
GO

ALTER VIEW UserMeter
AS
SELECT DISTINCT
    UserAccount.Name AS UserName,
    Meter.ID AS MeterID
FROM
    UserAccount JOIN
    UserAccountAssetGroup ON UserAccountAssetGroup.UserAccountID = UserAccount.ID LEFT OUTER JOIN
    MeterAssetGroup ON MeterAssetGroup.AssetGroupID = UserAccountAssetGroup.AssetGroupID LEFT OUTER JOIN
    LineAssetGroup ON LineAssetGroup.AssetGroupID = UserAccountAssetGroup.AssetGroupID LEFT OUTER JOIN
    MeterLine ON MeterLine.LineID = LineAssetGroup.LineID JOIN
    Meter ON
        MeterAssetGroup.MeterID = Meter.ID OR
        MeterLine.MeterID = Meter.ID
WHERE
    UserAccount.Approved <> 0 AND
    UserAccountAssetGroup.Dashboard <> 0
GO

ALTER VIEW DisturbanceView
AS
SELECT
    Disturbance.ID,
    Disturbance.EventID,
    Disturbance.EventTypeID,
    Disturbance.PhaseID,
    Disturbance.Magnitude,
    Disturbance.PerUnitMagnitude,
    Disturbance.StartTime,
    Disturbance.EndTime,
    Disturbance.DurationSeconds,
    Disturbance.DurationCycles,
    Disturbance.StartIndex,
    Disturbance.EndIndex,
    Event.MeterID,
    (
        SELECT MAX(SeverityCode) AS Expr1
        FROM DisturbanceSeverity
        WHERE (DisturbanceID = Disturbance.ID)
    ) AS SeverityCode,
    Meter.Name AS MeterName,
    Phase.Name AS PhaseName,
    Event.LineID
FROM
    Disturbance JOIN
    Event ON Disturbance.EventID = Event.ID JOIN
    Meter ON Event.MeterID = Meter.ID JOIN
    Phase ON Disturbance.PhaseID = Phase.ID
GO

CREATE VIEW BreakerView
AS
SELECT
    BreakerOperation.ID,
    Meter.ID AS MeterID,
    Event.ID AS EventID,
    EventType.Name AS EventType,
    BreakerOperation.TripCoilEnergized AS Energized,
    BreakerOperation.BreakerNumber,
    MeterLine.LineName,
    Phase.Name AS PhaseName,
    CAST(BreakerOperation.BreakerTiming AS DECIMAL(16, 5)) AS Timing,
    BreakerOperation.BreakerSpeed AS Speed,
    BreakerOperationType.Name AS OperationType,
    BreakerOperation.UpdatedBy
FROM
    BreakerOperation JOIN
    Event ON BreakerOperation.EventID = Event.ID JOIN
    EventType ON EventType.ID = Event.EventTypeID JOIN
    Meter ON Meter.ID = Event.MeterID JOIN
    Line ON Line.ID = Event.LineID JOIN
    MeterLine ON MeterLine.LineID = Event.LineID AND MeterLine.MeterID = Meter.ID JOIN
    BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID JOIN
    Phase ON BreakerOperation.PhaseID = Phase.ID
GO

ALTER VIEW FaultView
AS
SELECT
    FaultSummary.ID AS ID,
    FaultSummary.EventID,
    FaultSummary.Algorithm,
    FaultSummary.FaultNumber,
    FaultSummary.CalculationCycle,
    FaultSummary.Distance,
    FaultSummary.CurrentMagnitude,
    FaultSummary.CurrentLag,
    FaultSummary.PrefaultCurrent,
    FaultSummary.PostfaultCurrent,
    FaultSummary.Inception,
    FaultSummary.DurationSeconds,
    FaultSummary.DurationCycles,
    FaultSummary.FaultType,
    FaultSummary.IsSelectedAlgorithm,
    FaultSummary.IsValid,
    FaultSummary.IsSuppressed,
    Meter.Name AS MeterName,
    Meter.ShortName AS ShortName,
    MeterLocation.ShortName AS LocationName,
    Meter.ID AS MeterID,
    Line.ID AS LineID,
    MeterLine.LineName AS LineName,
    Line.VoltageKV AS Voltage,
    Event.StartTime AS InceptionTime,
    CASE WHEN FaultSummary.Distance = '-1E308'
        THEN 'NaN'
        ELSE CAST(CAST(FaultSummary.Distance AS DECIMAL(16,2)) AS NVARCHAR(19))
    END AS CurrentDistance,
    ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY FaultSummary.IsSuppressed, FaultSummary.IsSelectedAlgorithm DESC, FaultSummary.Inception) AS RK
FROM
    FaultSummary JOIN
    Event ON FaultSummary.EventID = Event.ID JOIN
    EventType ON Event.EventTypeID = EventType.ID JOIN
    Meter ON Event.MeterID = Meter.ID JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
    Line ON Event.LineID = Line.ID JOIN
    MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Line.ID
WHERE
    EventType.Name = 'Fault'
GO

ALTER VIEW WorkbenchVoltageCurveView
AS
SELECT
    WorkbenchVoltageCurve.ID,
    WorkbenchVoltageCurve.Name,
    WorkbenchVoltageCurvePoint.ID AS CurvePointID,
    WorkbenchVoltageCurvePoint.PerUnitMagnitude,
    WorkbenchVoltageCurvePoint.DurationSeconds,
    WorkbenchVoltageCurvePoint.LoadOrder,
    WorkbenchVoltageCurve.Visible
FROM
    WorkbenchVoltageCurve JOIN
    WorkbenchVoltageCurvePoint ON
        WorkbenchVoltageCurve.ID = WorkbenchVoltageCurvePoint.VoltageCurveID AND
        WorkbenchVoltageCurve.ID = WorkbenchVoltageCurvePoint.VoltageCurveID AND
        WorkbenchVoltageCurve.ID = WorkbenchVoltageCurvePoint.VoltageCurveID
GO

ALTER VIEW EmailTypeView
AS
SELECT
    EmailType.EmailCategoryID,
    EmailType.XSLTemplateID,
    EmailType.ID,
    EmailCategory.Name AS EmailCategory,
    XSLTemplate.Name AS XSLTemplate,
    EmailCategory.Name + ' - ' + XSLTemplate.Name AS Name
FROM
    EmailType JOIN
    EmailCategory ON EmailType.EmailCategoryID = EmailCategory.ID JOIN
    XSLTemplate ON EmailType.XSLTemplateID = XSLTemplate.ID
GO

ALTER VIEW AuditLogView
AS
SELECT TOP(2000)
    ID,
    TableName,
    PrimaryKeyColumn,
    PrimaryKeyValue,
    ColumnName,
    OriginalValue,
    NewValue,
    Deleted,
    UpdatedBy,
    UpdatedOn
FROM AuditLog
WHERE UpdatedBy IS NOT NULL
GO

ALTER VIEW MetersWithHourlyLimits
AS
SELECT
    Meter.Name,
    COUNT(DISTINCT Channel.ID) AS Limits,
    Meter.ID
FROM
    HourOfWeekLimit JOIN
    Channel ON HourOfWeekLimit.ChannelID = Channel.ID JOIN
    Meter ON Channel.MeterID = Meter.ID
GROUP BY
    Meter.Name,
    Meter.ID
GO

ALTER VIEW HourOfWeekLimitView
AS
SELECT
    HourOfWeekLimit.ID,
    HourOfWeekLimit.ChannelID,
    HourOfWeekLimit.AlarmTypeID,
    HourOfWeekLimit.HourOfWeek,
    HourOfWeekLimit.Severity,
    HourOfWeekLimit.High,
    HourOfWeekLimit.Low,
    HourOfWeekLimit.Enabled, AlarmType.Name AS AlarmTypeName
FROM
    HourOfWeekLimit JOIN
    AlarmType ON HourOfWeekLimit.AlarmTypeID = AlarmType.ID
GO

ALTER VIEW ChannelsWithHourlyLimits
AS
SELECT
    Channel.Name,
    COUNT(DISTINCT HourOfWeekLimit.HourOfWeek) AS Limits,
    Channel.ID,
    Channel.MeterID,
    AlarmType.Name AS AlarmTypeName,
    MeasurementCharacteristic.Name AS MeasurementCharacteristic,
    MeasurementType.Name AS MeasurementType,
    Channel.HarmonicGroup,
    Phase.Name AS Phase
FROM
    HourOfWeekLimit JOIN
    Channel ON HourOfWeekLimit.ChannelID = Channel.ID JOIN
    AlarmType ON HourOfWeekLimit.AlarmTypeID = AlarmType.ID JOIN
    Meter ON Channel.MeterID = Meter.ID JOIN
    MeasurementCharacteristic ON Channel.MeasurementCharacteristicID = MeasurementCharacteristic.ID JOIN
    MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID JOIN
    Phase ON Channel.PhaseID = Phase.ID
GROUP BY
    Channel.Name,
    Channel.MeterID,
    Channel.ID,
    AlarmType.Name,
    MeasurementCharacteristic.Name,
    MeasurementType.Name,
    Channel.HarmonicGroup,
    Phase.Name
GO

ALTER VIEW MetersWithNormalLimits
AS
SELECT
    Meter.Name,
    COUNT(DISTINCT Channel.ID) AS Limits,
    Meter.ID
FROM
    AlarmRangeLimit JOIN
    Channel ON AlarmRangeLimit.ChannelID = Channel.ID JOIN
    Meter ON Channel.MeterID = Meter.ID
GROUP BY
    Meter.Name,
    Meter.ID
GO

ALTER VIEW ChannelsWithNormalLimits
AS
SELECT
    Channel.Name,
    Channel.ID,
    Channel.MeterID,
    AlarmType.Name AS AlarmTypeName,
    MeasurementCharacteristic.Name AS MeasurementCharacteristic,
    MeasurementType.Name AS MeasurementType,
    Channel.HarmonicGroup,
    Phase.Name AS Phase,
    High,
    Low,
    RangeInclusive,
    PerUnit,
    AlarmRangeLimit.Enabled,
    IsDefault
FROM
    AlarmRangeLimit JOIN
    Channel ON AlarmRangeLimit.ChannelID = Channel.ID JOIN
    AlarmType ON AlarmRangeLimit.AlarmTypeID = AlarmType.ID JOIN
    Meter ON Channel.MeterID = Meter.ID JOIN
    MeasurementCharacteristic ON Channel.MeasurementCharacteristicID = MeasurementCharacteristic.ID JOIN
    MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID JOIN
    Phase ON Channel.PhaseID = Phase.ID
GO

ALTER PROCEDURE [dbo].[selectBreakersForCalendar]
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

DECLARE @counter INT = 0
DECLARE @eventDate DATE = (Select max(CAST(StartTime AS Date)) from Event) --'2015-03-23'
DECLARE @numberOfDays INT = DATEDIFF ( day , (Select min(CAST(StartTime AS Date)) from Event), @eventDate) --365*5

SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate)

CREATE TABLE #temp(Date DATE)

WHILE (@counter <= @numberOfDays)
BEGIN
    INSERT INTO #temp VALUES(@eventDate)
    SET @eventDate = DATEADD(DAY, 1, @eventDate)
    SET @counter = @counter + 1
END

SELECT Date as thedate, Normal as normal, late as late, indeterminate as indeterminate
FROM
(
    SELECT #temp.Date, [BreakerOperationType].Name AS EventTypeName, COALESCE(EventCount, 0) AS EventCount
    FROM
        #temp CROSS JOIN
        [BreakerOperationType] LEFT OUTER JOIN
        (
            SELECT CAST([TripCoilEnergized] AS Date) AS EventDate, [BreakerOperationTypeID] as EventTypeID, COUNT(*) AS EventCount
            FROM [BreakerOperation] join [Event] on [BreakerOperation].[EventID] = [Event].[ID] 
            GROUP BY CAST([TripCoilEnergized] AS Date), [BreakerOperationTypeID]
        ) AS Event ON #temp.Date = Event.EventDate AND [BreakerOperationType].ID = Event.EventTypeID
) AS EventDate
PIVOT
(
    SUM(EventCount)
    FOR EventDate.EventTypeName IN ( normal , late , indeterminate )
) as pvt
ORDER BY Date

DROP TABLE #temp

END
GO

ALTER PROCEDURE [dbo].[selectSiteLinesDisturbanceDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @worstPhaseID INT = (SELECT ID FROM Phase WHERE Name = 'Worst')
    DECLARE @startDate DATETIME = @EventDate
    DECLARE @endDate DATETIME


    IF @context = 'day'
    BEGIN
        SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @endDate = DATEADD(DAY, 1, @startDate)
    END

    if @context = 'hour'
    BEGIN
        SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0)
        SET @endDate = DATEADD(HOUR, 1, @startDate)
    END

    if @context = 'minute'
    BEGIN
        SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0)
        SET @endDate = DATEADD(MINUTE, 1, @startDate)
    END

    if @context = 'second'
    BEGIN
        DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate)
        SET @endDate = DATEADD(SECOND, 1, @startDate)
    END

    DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope')

    SELECT
        Event.LineID AS thelineid,
        Event.ID AS theeventid,
        Disturbance.ID as disturbanceid,
        EventType.Name AS disturbancetype,
        Phase.Name AS phase,
        CASE Disturbance.PerUnitMagnitude
            WHEN -1E308 THEN 'NaN'
            ELSE CAST(Disturbance.PerUnitMagnitude AS VARCHAR(MAX))
        END AS magnitude,
        CASE Disturbance.DurationSeconds
            WHEN -1E308 THEN 'NaN'
            ELSE CAST(CONVERT(DECIMAL(10,3), Disturbance.DurationSeconds) AS VARCHAR(14))
        END AS duration,
        CAST(Disturbance.StartTime AS VARCHAR(26)) AS theinceptiontime,
        dbo.DateDiffTicks('1970-01-01', Disturbance.StartTime) / 10000.0 AS startmillis,
        dbo.DateDiffTicks('1970-01-01', Disturbance.EndTime) / 10000.0 AS endmillis,
        DisturbanceSeverity.SeverityCode,
        MeterLine.LineName + ' ' + [Line].[AssetKey] AS thelinename,
        Line.VoltageKV AS voltage,
        (SELECT COUNT(*) FROM EventNote WHERE EventID = Event.ID) as notes
    FROM
        Event JOIN
        Disturbance ON Disturbance.EventID = Event.ID JOIN
        Disturbance WorstDisturbance ON
            Disturbance.EventID = WorstDisturbance.EventID AND
            Disturbance.PerUnitMagnitude = WorstDisturbance.PerUnitMagnitude AND
            Disturbance.DurationSeconds = WorstDisturbance.DurationSeconds JOIN
        EventType ON Disturbance.EventTypeID = EventType.ID JOIN
        Phase ON Disturbance.PhaseID = Phase.ID JOIN
        DisturbanceSeverity ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN
        Meter ON Meter.ID = @MeterID JOIN
        Line ON Event.LineID = Line.ID JOIN
        MeterLine ON MeterLine.MeterID = @MeterID AND MeterLine.LineID = Line.ID JOIN
        VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID
    WHERE
        Event.StartTime >= @startDate AND Event.StartTime < @endDate AND
        Event.MeterID = @MeterID AND
        WorstDisturbance.PhaseID = @worstPhaseID AND
        Disturbance.PhaseID <> @worstPhaseID AND
        VoltageEnvelope.Name = COALESCE(@voltageEnvelope, 'ITIC')
    ORDER BY
        Event.StartTime ASC

END
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Breakers.MaxCyclesBeforeRestrike', '2.0', '2.0')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Breakers.MinCyclesBeforeRestrike', '0.125', '0.125')
GO

UPDATE Setting SET Name = 'COMTRADE.MinWaitTime' WHERE Name = 'COMTRADEMinWaitTime'
GO

UPDATE Setting SET Name = 'COMTRADE.UseRelaxedValidation' WHERE Name = 'COMTRADEUseRelaxedValidation'
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('COMTRADE.WaitForINF', 'False', 'False')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Email.AdminAddress', 'xda-admin@gridprotectionalliance.org', 'xda-admin@gridprotectionalliance.org')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Email.BlindCopyAddress', '', '')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('EventEmail.Enabled', 'False', 'False')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('EventEmail.MaxEmailCount', '0', '0')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('EventEmail.MaxEmailSpan', '0.0', '0.0')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FaultLocation.FaultCalculationCycleMethod', 'MaxCurrent', 'MaxCurrent')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FaultLocation.FaultClearingAdjustmentSamples', '10', '10')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FaultLocation.FaultedVoltageThreshold', '0.8', '0.8')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FaultLocation.GroundedFaultVoltageThreshold', '0.001', '0.001')
GO

UPDATE Setting SET Name = 'PQMarkAggregation.Enabled' WHERE Name = 'PQMarkAggregation.Enable'
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('PQTrendingWebReport.Enabled', 'False', 'False')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('PQTrendingWebReport.Frequency', '0 2 * * *', '0 2 * * *')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('PQTrendingWebReport.Verbose', 'False', 'False')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('StepChangeWebReport.Enabled', 'False', 'False')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('StepChangeWebReport.Frequency', '0 2 * * *', '0 2 * * *')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('StepChangeWebReport.Verbose', 'False', 'False')
GO

INSERT INTO DashSettings (Name, Value, Enabled) VALUES ('EventsChart', 'BreakerOpen', 1)
GO

INSERT INTO DashSettings (Name, Value, Enabled) VALUES ('EventsChart', 'Snapshot', 1)
GO

INSERT INTO DashSettings (Name, Value, Enabled) VALUES ('EventsChartColors', 'BreakerOpen,#B245BA', 1)
GO

INSERT INTO DashSettings (Name, Value, Enabled) VALUES ('EventsChartColors', 'Snapshot,#9db087', 1)
GO

INSERT INTO DashSettings (Name, Value, Enabled) VALUES ('System.URL', 'http://localhost/PQDashboard', 1)
GO

INSERT INTO Phase(Name, Description)
SELECT 'NG', 'Neutral to ground'
WHERE NOT EXISTS (SELECT * FROM Phase WHERE Name = 'NG')
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average RMS Voltage Phase A', 'Average RMS Voltage Phase A', (SELECT ID FROM Unit WHERE Name = 'Volts'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'RMS'), (SELECT ID FROM Phase WHERE Name = 'AN'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average RMS Voltage Phase B', 'Average RMS Voltage Phase B', (SELECT ID FROM Unit WHERE Name = 'Volts'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'RMS'), (SELECT ID FROM Phase WHERE Name = 'BN'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average RMS Voltage Phase C', 'Average RMS Voltage Phase C', (SELECT ID FROM Unit WHERE Name = 'Volts'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'RMS'), (SELECT ID FROM Phase WHERE Name = 'CN'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Voltage Unbalance(S2/S1)', 'Average Voltage Unbalance(S2/S1)', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S2S1'), (SELECT ID FROM Phase WHERE Name = 'None'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Voltage Unbalance(S0/S1)', 'Average Voltage Unbalance(S0/S1)', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S0S1'), (SELECT ID FROM Phase WHERE Name = 'None'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Pst Flicker Phase A', 'Pst Flicker Phase A', (SELECT ID FROM Unit WHERE Name = 'Per Unit'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'FlkrPST'), (SELECT ID FROM Phase WHERE Name = 'AN'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Pst Flicker Phase B', 'Pst Flicker Phase B', (SELECT ID FROM Unit WHERE Name = 'Per Unit'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'FlkrPST'), (SELECT ID FROM Phase WHERE Name = 'BN'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Pst Flicker Phase C', 'Pst Flicker Phase C', (SELECT ID FROM Unit WHERE Name = 'Per Unit'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'FlkrPST'), (SELECT ID FROM Phase WHERE Name = 'CN'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Vthd Phase A', 'Average Vthd Phase A', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'), (SELECT ID FROM Phase WHERE Name = 'AN'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Vthd Phase B', 'Average Vthd Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'), (SELECT ID FROM Phase WHERE Name = 'BN'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Vthd Phase C', 'Average Vthd Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'), (SELECT ID FROM Phase WHERE Name = 'CN'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H3 Phase A', 'Average V H3 Phase A', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 3)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H3 Phase B', 'Average V H3 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 3)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H3 Phase C', 'Average V H3 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 3)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H5 Phase A', 'Average V H5 Phase A', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 5)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H5 Phase B', 'Average V H5 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 5)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H5 Phase C', 'Average V H5 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 5)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H7 Phase A', 'Average V H7 Phase A', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 7)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H7 Phase B', 'Average V H7 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 7)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H7 Phase C', 'Average V H7 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 7)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H11 Phase A', 'Average V H11 Phase A', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 11)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H11 Phase B', 'Average V H11 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 11)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H11 Phase C', 'Average V H11 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 11)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H13 Phase A', 'Average V H13 Phase A', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 13)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H13 Phase B', 'Average V H13 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 13)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average V H13 Phase C', 'Average V H13 Phase C', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 13)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Current Unbalance(S2/S1)', 'Average Current Unbalance(S2/S1)', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S2S1'), (SELECT ID FROM Phase WHERE Name = 'None'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Current Unbalance(S0/S1)', 'Average Current Unbalance(S0/S1)', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S0S1'), (SELECT ID FROM Phase WHERE Name = 'None'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Ithd Phase A', 'Average Ithd Phase A', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'), (SELECT ID FROM Phase WHERE Name = 'AN'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Ithd Phase B', 'Average Ithd Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'), (SELECT ID FROM Phase WHERE Name = 'BN'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Ithd Phase C', 'Average Ithd Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'), (SELECT ID FROM Phase WHERE Name = 'CN'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Ithd Phase N', 'Average Ithd Phase N', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'), (SELECT ID FROM Phase WHERE Name = 'NG'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average Ithd Phase RES', 'Average Ithd Phase RES', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'), (SELECT ID FROM Phase WHERE Name = 'RES'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H3 Phase A', 'Average I H3 Phase A', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 3)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H3 Phase B', 'Average I H3 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 3)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H3 Phase C', 'Average I H3 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 3)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H3 Phase N', 'Average I H3 Phase N', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'NG'), 3)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H3 Phase RES', 'Average I H3 Phase RES', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'RES'), 3)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H5 Phase A', 'Average I H5 Phase A', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 5)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H5 Phase B', 'Average I H5 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 5)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H5 Phase C', 'Average I H5 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 5)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H5 Phase N', 'Average I H5 Phase N', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'NG'), 5)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H5 Phase RES', 'Average I H5 Phase RES', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'RES'), 5)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H7 Phase A', 'Average I H7 Phase A', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 7)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H7 Phase B', 'Average I H7 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 7)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H7 Phase C', 'Average I H7 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 7)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H7 Phase N', 'Average I H7 Phase N', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'NG'), 7)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H7 Phase RES', 'Average I H7 Phase RES', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'RES'), 7)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H11 Phase A', 'Average I H11 Phase A', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 11)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H11 Phase B', 'Average I H11 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 11)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H11 Phase C', 'Average I H11 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 11)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H11 Phase N', 'Average I H11 Phase N', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'NG'), 11)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H11 Phase RES', 'Average I H11 Phase RES', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'RES'), 11)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H13 Phase A', 'Average I H13 Phase A', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'AN'), 13)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H13 Phase B', 'Average I H13 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'BN'), 13)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H13 Phase C', 'Average I H13 Phase C', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'CN'), 13)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H13 Phase N', 'Average I H13 Phase N', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'NG'), 13)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Average I H13 Phase RES', 'Average I H13 Phase RES', (SELECT ID FROM Unit WHERE Name = 'Amps'), (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'), (SELECT ID FROM Phase WHERE Name = 'RES'), 13)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('P', 'P', (SELECT ID FROM Unit WHERE Name = 'KW'), (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'P'), (SELECT ID FROM Phase WHERE Name = 'LineToNeutralAverage'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('Q', 'Q', (SELECT ID FROM Unit WHERE Name = 'KVAR'), (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'Q'), (SELECT ID FROM Phase WHERE Name = 'LineToNeutralAverage'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('S', 'S', (SELECT ID FROM Unit WHERE Name = 'KVA'), (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S'), (SELECT ID FROM Phase WHERE Name = 'LineToNeutralAverage'), 0)
GO

INSERT INTO PQMeasurement(Name, Description, UnitID, MeasurementTypeID, MeasurementCharacteristicID, PhaseID, HarmonicGroup) VALUES('PF', 'PF', (SELECT ID FROM Unit WHERE Name = 'Percent'), (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'PF'), (SELECT ID FROM Phase WHERE Name = 'LineToNeutralAverage'), 0)
GO

INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average RMS Voltage Phase A'), 5)
GO

INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average RMS Voltage Phase B'), 5)
GO

INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average RMS Voltage Phase C'), 5)
GO

INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Pst Flicker Phase A'), 5)
GO

INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Pst Flicker Phase B'), 5)
GO

INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Pst Flicker Phase C'), 5)
GO

INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average Vthd Phase A'), 5)
GO

INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average Vthd Phase B'), 5)
GO

INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average Vthd Phase C'), 5)
GO

INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average Voltage Unbalance(S2/S1)'), 5)
GO

INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average Voltage Unbalance(S0/S1)'), 5)
GO

INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average Current Unbalance(S2/S1)'), 5)
GO

INSERT INTO StepChangeMeasurement (PQMeasurementID, Setting) VALUES ((SELECT ID FROM PQMeasurement WHERE Name = 'Average Current Unbalance(S0/S1)'), 5)
GO

DROP TABLE MeterFileGroup
GO

CREATE TABLE MeterConfiguration
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    MeterID INT NOT NULL REFERENCES Meter(ID),
    DiffID INT NULL REFERENCES MeterConfiguration(ID),
    ConfigKey VARCHAR(50) NOT NULL,
    ConfigText VARCHAR(MAX) NOT NULL
)
GO

CREATE NONCLUSTERED INDEX IX_MeterConfiguration_MeterID
ON MeterConfiguration(MeterID)
GO

CREATE NONCLUSTERED INDEX IX_MeterConfiguration_DiffID
ON MeterConfiguration(DiffID)
GO

CREATE TABLE FileGroupMeterConfiguration
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    FileGroupID INT NOT NULL REFERENCES FileGroup(ID),
    MeterConfigurationID INT NOT NULL REFERENCES MeterConfiguration(ID)
)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupMeterConfiguration_FileGroupID
ON FileGroupMeterConfiguration(FileGroupID)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupMeterConfiguration_MeterConfigurationID
ON FileGroupMeterConfiguration(MeterConfigurationID)
GO

CREATE TABLE BreakerRestrike
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    EventID INT NOT NULL REFERENCES Event(ID),
    PhaseID INT NOT NULL REFERENCES Phase(ID),
    Sample INT NOT NULL,
    Timestamp DATETIME2 NOT NULL
)
GO

CREATE NONCLUSTERED INDEX IX_BreakerRestrike_EventID
ON BreakerRestrike(EventID ASC)
GO

CREATE NONCLUSTERED INDEX IX_BreakerRestrike_Timestamp
ON BreakerRestrike(Timestamp ASC)
GO

CREATE TABLE NoteType(
	ID int not null IDENTITY(1,1) PRIMARY KEY,
	Name varchar(max) not null,
	ReferenceTableName varchar(max) not null,
)
GO

INSERT INTO NoteType (Name, ReferenceTableName) VALUES ('Meter', 'Meter')
GO
INSERT INTO NoteType (Name, ReferenceTableName) VALUES ('Event', 'Event')
GO

CREATE TABLE Note (
	ID int not null IDENTITY(1,1) PRIMARY KEY,
	NoteTypeID int Not NULL REFERENCES NoteType(ID),
	ReferenceTableID INT NOT NULL,
    Note VARCHAR(MAX) NOT NULL,
    UserAccount VARCHAR(MAX) NOT NULL DEFAULT SUSER_NAME(),
    Timestamp DATETIME NOT NULL DEFAULT GETUTCDATE(),
)
GO

CREATE NONCLUSTERED INDEX IX_Note_NoteTypeID_ReferenceTableID  
ON Note(NoteTypeID, ReferenceTableID)
GO

ALTER TABLE LinesToDataPush
ADD RemoteLineCreatedByDataPusher bit NOT NULL DEFAULT (1)
GO

CREATE TABLE RelayAlertSetting
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    LineID INT NOT NULL REFERENCES Line(ID),
	TripTime INT NULL,
	PickupTime INT NULL,
	TripCoilCondition FLOAT NULL,
)
GO

CREATE TABLE PQMarkCompanyCustomer
(
	ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	PQMarkCompanyID INT NOT NULL FOREIGN KEY REFERENCES PQMarkCompany(ID),
	CustomerID INT NOT NULL
)
GO

CREATE NONCLUSTERED INDEX IX_PQMarkCompanyMeter_MeterID 
ON PQMarkCompanyMeter(MeterID) 
GO

CREATE TABLE RelayPerformance
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    EventID INT NOT NULL REFERENCES Event(ID),
	ChannelID INT NOT NULL REFERENCES Channel(ID),
	Imax1 FLOAT NULL,
	Imax2 FLOAT NULL,
    TripInitiate DATETIME2 NULL,
	TripTime INT NULL,
	PickupTime INT NULL,
	TripCoilCondition FLOAT NULL,
)
GO

CREATE VIEW BreakerHistory
AS
SELECT  Line.ID AS LineID,
		RelayPerformance.EventID AS EventID,
        RelayPerformance.Imax1,
		RelayPerformance.Imax2,
		RelayPerformance.TripInitiate,
		RelayPerformance.TripTime / 10 AS TripTime,
		RelayPerformance.PickupTime / 10 AS PickupTime,
		RelayPerformance.TripCoilCondition,
		RelayAlertSetting.TripCoilCondition AS TripCoilConditionAlert,
		RelayAlertSetting.TripTime AS TripTimeAlert,
		RelayAlertSetting.PickupTime AS PickupTimeAlert,
		RelayPerformance.ChannelID AS TripCoilChannelID
FROM    RelayPerformance LEFT OUTER JOIN
        Channel ON RelayPerformance.ChannelID = Channel.ID LEFT OUTER JOIN
        Line ON Channel.LineID = Line.ID LEFT OUTER JOIN 
		RelayAlertSetting ON RelayAlertSetting.LineID = Line.ID
GO

ALTER VIEW LineView
AS
SELECT
    Line.ID,
    Line.AssetKey,
    Line.VoltageKV,
    Line.ThermalRating,
    Line.Length,
    COALESCE(Line.MaxFaultDistance, Line.Length * MaxFaultDistanceMultiplier.Value) MaxFaultDistance,
    COALESCE(Line.MinFaultDistance, Line.Length * MinFaultDistanceMultiplier.Value) MinFaultDistance,
    Line.Description,
    (
        SELECT TOP 1 LineName
        FROM MeterLine
        WHERE LineID = Line.ID
    ) AS TopName,
    LineImpedance.R0,
    LineImpedance.X0,
    LineImpedance.R1,
    LineImpedance.X1,
    LineImpedance.ID AS LineImpedanceID,
	RelayAlertSetting.TripTime,
	RelayAlertSetting.PickupTime,
	RelayAlertSetting.TripCoilCondition,
	RelayAlertSetting.ID AS RelayAlertSettingID
FROM
    Line LEFT OUTER JOIN
    LineImpedance ON Line.ID = LineImpedance.LineID LEFT OUTER JOIN
	RelayAlertSetting ON Line.ID = RelayAlertSetting.LineID CROSS JOIN
    (SELECT COALESCE((SELECT Value FROM Setting WHERE Name = 'FaultLocation.MaxFaultDistanceMultiplier'), 1.05) Value) MaxFaultDistanceMultiplier CROSS JOIN
    (SELECT COALESCE((SELECT Value FROM Setting WHERE Name = 'FaultLocation.MinFaultDistanceMultiplier'), 1.05) Value) MinFaultDistanceMultiplier

GO

CREATE VIEW OpenSEEScalarStatView AS
SELECT
    Event.ID AS EventID,
    MeterLocation.Name AS Station,
    Meter.Name AS Meter,
    Line.AssetKey AS LineKey,
    MeterLine.LineName,
    EventType.Name AS [Event Type],
    FORMAT(DATEDIFF(MILLISECOND, Event.StartTime, Event.EndTime) / 1000.0, '0.###') AS [File Duration (seconds)],
    FORMAT(DATEDIFF(MILLISECOND, Event.StartTime, Event.EndTime) * System.Frequency / 1000.0, '0.##') AS [File Duration (cycles)],
    FORMAT(FaultSummary.Distance, '0.##') AS [Fault Distance (mi)],
    FORMAT(FaultSummary.DurationSeconds * 1000.0, '0') AS [Fault Duration (ms)],
    FORMAT(FaultSummary.DurationCycles, '0.##') AS [Fault Duration (cycles)],
    FORMAT(Sag.MagnitudePercent, '0.0') AS [Sag Magnitude (%)],
    FORMAT(Sag.MagnitudeVolts, '0') AS [Sag Magnitude (RMS volts)],
    FaultSummary.Algorithm,
    FORMAT(EventStat.VPeak, '0') AS [Voltage Peak (volts)],
    FORMAT(EventStat.VAMax, '0') AS [VA Maximum (RMS volts)],
    FORMAT(EventStat.VBMax, '0') AS [VB Maximum (RMS volts)],
    FORMAT(EventStat.VCMax, '0') AS [VC Maximum (RMS volts)],
    FORMAT(EventStat.VABMax, '0') AS [VAB Maximum (RMS volts)],
    FORMAT(EventStat.VBCMax, '0') AS [VBC Maximum (RMS volts)],
    FORMAT(EventStat.VCAMax, '0') AS [VCA Maximum (RMS volts)],
    FORMAT(EventStat.VAMin, '0') AS [VA Minimum (RMS volts)],
    FORMAT(EventStat.VBMin, '0') AS [VB Minimum (RMS volts)],
    FORMAT(EventStat.VCMin, '0') AS [VC Minimum (RMS volts)],
    FORMAT(EventStat.VABMin, '0') AS [VAB Minimum (RMS volts)],
    FORMAT(EventStat.VBCMin, '0') AS [VBC Minimum (RMS volts)],
    FORMAT(EventStat.VCAMin, '0') AS [VCA Minimum (RMS volts)],
    FORMAT(EventStat.IPeak, '0') AS [Current Peak (Amps)],
    FORMAT(EventStat.IAMax, '0') AS [IA Maximum (RMS Amps)],
    FORMAT(EventStat.IBMax, '0') AS [IB Maximum (RMS Amps)],
    FORMAT(EventStat.ICMax, '0') AS [IC Maximum (RMS Amps)],
    FORMAT(EventStat.IA2t, '0') AS [IA I2t (A2s)],
    FORMAT(EventStat.IB2t, '0') AS [IB I2t (A2s)],
    FORMAT(EventStat.IC2t, '0') AS [IC I2t (A2s)],
    VAN.Mapping AS [VAN Channel],
    VBN.Mapping AS [VBN Channel],
    VCN.Mapping AS [VCN Channel],
    IAN.Mapping AS [IAN Channel],
    IBN.Mapping AS [IBN Channel],
    ICN.Mapping AS [ICN Channel],
    IR.Mapping AS [IR Channel],
	FORMAT(RP.Imax1, '0.000') AS [Lmax 1],
	FORMAT(RP.Imax2, '0.000') AS [Lmax 2],
	FORMAT(RP.TripInitiate,'HH:mm:ss.fff') AS [Trip Initiation],
	(RP.TripTime / 10) AS [Trip Time (microsec)],
	(RP.PickupTime / 10) AS [Pickup Time (microsec)],
	FORMAT(RP.TripCoilCondition, '0.000') AS [Trip Coil Condition (Aps)]
FROM
    Event JOIN
    MeterLine ON
        Event.MeterID = MeterLine.MeterID AND
        Event.LineID = MeterLine.LineID JOIN
    Meter ON Event.MeterID = Meter.ID JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
    Line ON Event.LineID = Line.ID JOIN
    EventType ON Event.EventTypeID = EventType.ID LEFT OUTER JOIN
    FaultSummary ON
        Event.ID = FaultSummary.EventID AND
        FaultSummary.IsSelectedAlgorithm <> 0 AND
        FaultSummary.FaultNumber = 1 LEFT OUTER JOIN
    EventStat ON Event.ID = EventStat.EventID LEFT OUTER JOIN
    ChannelDetail VAN ON
        Event.MeterID = VAN.MeterID AND
        Event.LineID = VAN.LineID AND
        VAN.MeasurementType = 'Voltage' AND
        VAN.Phase = 'AN' AND
        VAN.MeasurementCharacteristic = 'Instantaneous' AND
        VAN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail VBN ON
        Event.MeterID = VBN.MeterID AND
        Event.LineID = VBN.LineID AND
        VBN.MeasurementType = 'Voltage' AND
        VBN.Phase = 'BN' AND
        VBN.MeasurementCharacteristic = 'Instantaneous' AND
        VBN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail VCN ON
        Event.MeterID = VCN.MeterID AND
        Event.LineID = VCN.LineID AND
        VCN.MeasurementType = 'Voltage' AND
        VCN.Phase = 'CN' AND
        VCN.MeasurementCharacteristic = 'Instantaneous' AND
        VCN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail IAN ON
        Event.MeterID = IAN.MeterID AND
        Event.LineID = IAN.LineID AND
        IAN.MeasurementType = 'Current' AND
        IAN.Phase = 'AN' AND
        IAN.MeasurementCharacteristic = 'Instantaneous' AND
        IAN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail IBN ON
        Event.MeterID = IBN.MeterID AND
        Event.LineID = IBN.LineID AND
        IBN.MeasurementType = 'Current' AND
        IBN.Phase = 'BN' AND
        IBN.MeasurementCharacteristic = 'Instantaneous' AND
        IBN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail ICN ON
        Event.MeterID = ICN.MeterID AND
        Event.LineID = ICN.LineID AND
        ICN.MeasurementType = 'Current' AND
        ICN.Phase = 'CN' AND
        ICN.MeasurementCharacteristic = 'Instantaneous' AND
        ICN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail IR ON
        Event.MeterID = IR.MeterID AND
        Event.LineID = IR.LineID AND
        IR.MeasurementType = 'Current' AND
        IR.Phase = 'RES' AND
        IR.MeasurementCharacteristic = 'Instantaneous' AND
        IR.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
	RelayPerformance RP ON
		Event.ID = RP.EventID AND
		RP.ChannelID IN 
		(
			SELECT ID fROM ChannelDetail RPD WHERE 
				Event.MeterID = RPD.MeterID AND
				Event.LineID = RPD.LineID
		) CROSS JOIN
    (
        SELECT COALESCE(CONVERT(FLOAT,
        (
            SELECT TOP 1 Value
            FROM Setting
            WHERE Name = 'SystemFrequency'
        )), 60.0) AS Frequency
    ) System OUTER APPLY
    (
        SELECT TOP 1
            Disturbance.PerUnitMagnitude * 100 AS MagnitudePercent,
            Disturbance.Magnitude AS MagnitudeVolts
        FROM
            Disturbance JOIN
            EventType ON
                Disturbance.EventTypeID = EventType.ID AND
                EventType.Name = 'Sag' JOIN
            Phase ON
                Disturbance.PhaseID = Phase.ID AND
                Phase.Name = 'Worst'
        WHERE
            Disturbance.EventID = Event.ID AND
            Disturbance.StartTime <= dbo.AdjustDateTime2(FaultSummary.Inception, FaultSummary.DurationSeconds) AND
            Disturbance.EndTime >= FaultSummary.Inception
    ) Sag
GO

ALTER PROCEDURE [dbo].[selectBreakersForMeterIDByDateRange]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

DECLARE @dateStatement NVARCHAR(200) = N'CAST(TripCoilEnergized AS Date)'
DECLARE @groupByStatement NVARCHAR(200) = N'CAST(TripCoilEnergized AS Date)'

IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
    SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,TripCoilEnergized), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(HOUR, TripCoilEnergized), DateAdd(HOUR,DatePart(HOUR,TripCoilEnergized), @EventDateFrom)'
END


if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
    SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,TripCoilEnergized), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(MINUTE, TripCoilEnergized), DateAdd(MINUTE,DatePart(MINUTE,TripCoilEnergized), @EventDateFrom)'
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
    SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,TripCoilEnergized), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(SECOND, TripCoilEnergized), DateAdd(SECOND,DatePart(SECOND,TripCoilEnergized), @EventDateFrom)'
END

DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''

create table #TEMP (Name varchar(max))
insert into #TEMP SELECT Name FROM (Select Distinct Name FROM BreakerOperationType) as t

SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(max)), '') + '],'
FROM #TEMP ORDER BY Name desc

SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(max)), '') + '],'
FROM #TEMP ORDER BY Name desc

DROP TABLE #TEMP

SET @SQLStatement =
'                                                                                                            ' +
' SELECT *                                                                                                   ' +
' INTO #selectedMeters                                                                                       ' +
' FROM String_To_Int_Table(@MeterID, '','')                                                                  ' +
'                                                                                                            ' +
' SELECT Date as thedate, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) +
' FROM (                                                                                                     ' +
'   SELECT ' + @dateStatement + '  AS Date,                                                          ' +
'          BreakerOperationType.Name,                                                                        ' +
'          COUNT(*) AS thecount                                                                              ' +
'   FROM BreakerOperation JOIN                                                                               ' +
'        BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID JOIN      ' +
'        Event ON Event.ID = BreakerOperation.EventID                                                        ' +
'   WHERE MeterID IN (SELECT * FROM #selectedMeters) AND                                                     ' +
'         TripCoilEnergized >= @startDate AND TripCoilEnergized < @endDate                                   ' +
'   GROUP BY ' + @groupByStatement + ', BreakerOperationType.Name                                        ' +
') as table1                                                                                                 ' +
' PIVOT(                                                                                                     ' +
'       SUM(table1.thecount)                                                                                 ' +
'       FOR table1.Name IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')                           ' +
' ) as pvt                                                                                                   ' +
' ORDER BY Date                                                                                   '

exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DateTime ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom


END
GO

ALTER PROCEDURE [dbo].[selectBreakersForMeterIDsByDate]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

declare  @MeterIDs TABLE (ID int);
INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',');

DECLARE @counter INT = 0
DECLARE @eventDate DATE = CAST(@EventDateTo AS Date)
DECLARE @numberOfDays INT = DATEDIFF ( day , CAST(@EventDateFrom AS Date) , @eventDate)

SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate)

CREATE TABLE #temp (thesiteid int, thesitename varchar(100))

INSERT INTO #temp Select [dbo].[Meter].[ID], [dbo].[Meter].[Name] from [dbo].[Meter]

SELECT thesiteid as siteid, thesitename as sitename , Normal as normal, Late as late, Indeterminate as indeterminate
FROM
(
    SELECT #temp.thesiteid, #temp.thesitename , [BreakerOperationType].Name AS EventTypeName, COALESCE(EventCount, 0) AS EventCount

    FROM
        #temp CROSS JOIN
        [BreakerOperationType] LEFT OUTER JOIN
        (
            SELECT MeterID, [BreakerOperationTypeID], COUNT(*) AS EventCount
            FROM [BreakerOperation] join [Event] on [BreakerOperation].[EventID] = [Event].[ID]
            where MeterID in (Select * from @MeterIDs)
            and (CAST([TripCoilEnergized] as Date) between @EventDateFrom and @EventDateTo)
            GROUP BY [BreakerOperationTypeID], MeterID
        ) AS E ON [BreakerOperationType].ID = E.[BreakerOperationTypeID] and E.MeterID = #temp.thesiteid
) AS EventDate
PIVOT
(
    SUM(EventCount)
    FOR EventDate.EventTypeName IN (normal, late, indeterminate)
) as pvt
ORDER BY sitename asc

DROP TABLE #temp

END
GO

ALTER PROCEDURE [dbo].[selectCompletenessForMeterIDByDateRange]
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE)
DECLARE @endDate DATE = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

SELECT *
INTO #selectedMeters
FROM String_To_Int_Table(@MeterID, ',')

SELECT  Date as thedate, COALESCE(First, 0) AS '> 100%', COALESCE(Second, 0) AS '98% - 100%', COALESCE(Third, 0) AS '90% - 97%', COALESCE(Fourth, 0) AS '70% - 89%', COALESCE(Fifth, 0) AS '50% - 69%', COALESCE(Sixth, 0) AS '>0% - 49%'
FROM
    (
        SELECT Date, CompletenessLevel, COUNT(*) AS MeterCount
        FROM
        (
            SELECT Date,
                    CASE
                        WHEN Completeness > 100.0 THEN 'First'
                        WHEN 98.0 <= Completeness AND Completeness <= 100.0 THEN 'Second'
                        WHEN 90.0 <= Completeness AND Completeness < 98.0 THEN 'Third'
                        WHEN 70.0 <= Completeness AND Completeness < 90.0 THEN 'Fourth'
                        WHEN 50.0 <= Completeness AND Completeness < 70.0 THEN 'Fifth'
                        WHEN 0.0 < Completeness AND Completeness < 50.0 THEN 'Sixth'
                    END AS CompletenessLevel
            FROM
            (
                SELECT Date, 100.0 * CAST(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints AS FLOAT) / CAST(NULLIF(ExpectedPoints, 0) AS FLOAT) AS Completeness
                FROM MeterDataQualitySummary
                WHERE Date BETWEEN @startDate AND @endDate AND MeterID IN (SELECT * FROM #selectedMeters)
            ) MeterDataQualitySummary
        ) MeterDataQualitySummary
        GROUP BY Date, CompletenessLevel
) MeterDataQualitySummary
PIVOT
(
    SUM(MeterDataQualitySummary.MeterCount)
    FOR MeterDataQualitySummary.CompletenessLevel IN (First, Second, Third, Fourth, Fifth, Sixth)
) as pvt
ORDER BY Date

END
GO

ALTER PROCEDURE [dbo].[selectCompletenessForMeterIDsByDate]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

declare  @MeterIDs TABLE (ID int);
INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',');

DECLARE @counter INT = 0
DECLARE @eventDate DATE = CAST(@EventDateTo AS Date)
DECLARE @numberOfDays INT = DATEDIFF ( day , CAST(@EventDateFrom AS Date) , @eventDate)

SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate)

CREATE TABLE #meters (
thesiteid int,
thesitename varchar(100)
)

INSERT INTO #meters Select
[dbo].[Meter].[ID] as thesiteid,
[dbo].[Meter].[Name] as thesitename
from [dbo].[Meter] where Meter.ID in (Select * from @MeterIDs)

DECLARE @thesiteid int
DECLARE @thesitename varchar(100)

CREATE TABLE #temp (
siteId int,
siteName varchar(100),
ExpectedPoints int,
GoodPoints int,
LatchedPoints int,
UnreasonablePoints int,
NoncongruentPoints int,
DuplicatePoints int
)

DECLARE db_cursor CURSOR FOR
SELECT thesiteid, thesitename FROM #meters

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @thesiteid , @thesitename

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp
SELECT
    @thesiteid,
    @thesitename,
    (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[ExpectedPoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as ExpectedPoints,
    (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[GoodPoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as GoodPoints,
    (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[LatchedPoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as LatchedPoints,
    (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[UnreasonablePoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as UnreasonablePoints,
    (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[NoncongruentPoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as NoncongruentPoints,
    (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[DuplicatePoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as DuplicatePoints

    FETCH NEXT FROM db_cursor INTO @thesiteid , @thesitename
END

CLOSE db_cursor
DEALLOCATE db_cursor

select * from #temp

drop Table #temp
drop Table #meters

END
GO

ALTER PROCEDURE [dbo].[selectCorrectnessForMeterIDByDateRange]
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE)
DECLARE @endDate DATE = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

SELECT *
INTO #selectedMeters
FROM String_To_Int_Table(@MeterID, ',')

SELECT  Date as thedate, COALESCE(First, 0) AS '> 100%', COALESCE(Second, 0) AS '98% - 100%', COALESCE(Third, 0) AS '90% - 97%', COALESCE(Fourth, 0) AS '70% - 89%', COALESCE(Fifth, 0) AS '50% - 69%', COALESCE(Sixth, 0) AS '>0% - 49%'
FROM
    (
        SELECT Date, CompletenessLevel, COUNT(*) AS MeterCount
        FROM
        (
            SELECT Date,
                    CASE
                        WHEN Correctness > 100.0 THEN 'First'
                        WHEN 98.0 <= Correctness AND Correctness <= 100.0 THEN 'Second'
                        WHEN 90.0 <= Correctness AND Correctness < 98.0 THEN 'Third'
                        WHEN 70.0 <= Correctness AND Correctness < 90.0 THEN 'Fourth'
                        WHEN 50.0 <= Correctness AND Correctness < 70.0 THEN 'Fifth'
                        WHEN 0.0 < Correctness AND Correctness < 50.0 THEN 'Sixth'
                    END AS CompletenessLevel
            FROM
            (
                SELECT Date, 100.0 * CAST(GoodPoints AS FLOAT) / CAST(NULLIF(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints, 0) AS FLOAT) AS Correctness
                FROM MeterDataQualitySummary
                WHERE Date BETWEEN @startDate AND @endDate AND MeterID IN (SELECT * FROM #selectedMeters)
            ) MeterDataQualitySummary
        ) MeterDataQualitySummary
        GROUP BY Date, CompletenessLevel
) MeterDataQualitySummary
PIVOT
(
    SUM(MeterDataQualitySummary.MeterCount)
    FOR MeterDataQualitySummary.CompletenessLevel IN (First, Second, Third, Fourth, Fifth, Sixth)
) as pvt
ORDER BY Date

END
GO

ALTER PROCEDURE [dbo].[selectCorrectnessForMeterIDsByDate]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

declare  @MeterIDs TABLE (ID int);
INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',');

DECLARE @counter INT = 0
DECLARE @eventDate DATE = CAST(@EventDateTo AS Date)
DECLARE @numberOfDays INT = DATEDIFF ( day , CAST(@EventDateFrom AS Date) , @eventDate)

SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate)

CREATE TABLE #meters (
thesiteid int,
thesitename varchar(100)
)

INSERT INTO #meters Select
[dbo].[Meter].[ID] as thesiteid,
[dbo].[Meter].[Name] as thesitename
from [dbo].[Meter] where Meter.ID in (Select * from @MeterIDs)

DECLARE @thesiteid int
DECLARE @thesitename varchar(100)

CREATE TABLE #temp (
siteId int,
siteName varchar(100),
ExpectedPoints int,
GoodPoints int,
LatchedPoints int,
UnreasonablePoints int,
NoncongruentPoints int,
DuplicatePoints int
)

DECLARE db_cursor CURSOR FOR
SELECT thesiteid, thesitename FROM #meters

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @thesiteid , @thesitename

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp
SELECT
    @thesiteid,
    @thesitename,
    (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[ExpectedPoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as ExpectedPoints,
    (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[GoodPoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as GoodPoints,
    (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[LatchedPoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as LatchedPoints,
    (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[UnreasonablePoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as UnreasonablePoints,
    (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[NoncongruentPoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as NoncongruentPoints,
    (Select Coalesce(SUM([dbo].[MeterDataQualitySummary].[DuplicatePoints]), 0) from [MeterDataQualitySummary] where @thesiteid = MeterID and CAST([Date] as Date) between @EventDateFrom and @EventDateTo) as DuplicatePoints

    FETCH NEXT FROM db_cursor INTO @thesiteid , @thesitename
END

CLOSE db_cursor
DEALLOCATE db_cursor

select * from #temp

drop Table #temp
drop Table #meters

END
GO

ALTER PROCEDURE [dbo].[selectDisturbancesDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @username as nvarchar(4000)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE)
    DECLARE @endDate DATE = CAST(@EventDateTo AS DATE)

    SELECT
        Meter.ID AS themeterid,
        Meter.Name AS thesite,
        MeterLocation.Latitude AS latitude,
        MeterLocation.Longitude AS longitude,
        COALESCE(Disturbance_Count, 0) AS Disturbance_Count,
        COALESCE([5], 0) AS [5],
        COALESCE([4], 0) AS [4],
        COALESCE([3], 0) AS [3],
        COALESCE([2], 0) AS [2],
        COALESCE([1], 0) AS [1],
        COALESCE([0], 0) AS [0]
    FROM
        Meter JOIN
        MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN
        (
            SELECT
                MeterID,
                6*[5] + 5*[4] + 4*[3] + 3*[2] + 2*[1] + 1*[0] AS Disturbance_Count,
                [5],
                [4],
                [3],
                [2],
                [1],
                [0]
            FROM
            (
                SELECT
                    Event.MeterID,
                    DisturbanceSeverity.SeverityCode
                FROM
                    DisturbanceSeverity JOIN
                    Disturbance ON DisturbanceSeverity.DisturbanceID = Disturbance.ID JOIN
                    Event ON Disturbance.EventID = Event.ID
                WHERE CAST(Disturbance.StartTime AS DATE) BETWEEN @startDate AND @endDate
            ) AS MeterSeverityCode
            PIVOT
            (
                COUNT(SeverityCode)
                FOR SeverityCode IN ([5], [4], [3], [2], [1], [0])
            ) AS PivotTable
        ) AS SeverityCount ON SeverityCount.MeterID = Meter.ID
END
GO

ALTER PROCEDURE [dbo].[selectDisturbancesForMeterIDByDate]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)

AS
BEGIN

    SET NOCOUNT ON;

declare  @MeterIDs TABLE (ID int);
INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',');

DECLARE @counter INT = 0
DECLARE @eventDate DATE = CAST(@EventDateTo AS Date)
DECLARE @numberOfDays INT = DATEDIFF ( day , CAST(@EventDateFrom AS Date) , @eventDate)
DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope')
SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate)

CREATE TABLE #temp (thesiteid int, thesitename varchar(100))

INSERT INTO #temp Select [dbo].[Meter].[ID], [dbo].[Meter].[Name] from [dbo].[Meter] where Meter.ID in (Select * from @MeterIDs)

SELECT thesiteid as siteid, thesitename as sitename, [5], [4], [3], [2], [1], [0]
FROM
(
    SELECT #temp.thesiteid, #temp.thesitename, SeverityCodes.SeverityCode AS SeverityCode, COALESCE(DisturbanceCount, 0) AS DisturbanceCount
    FROM
        #temp Cross JOIN
        ( Select 5 as SeverityCode UNION
          SELECT 4 as SeverityCode UNION
          SELECT 3 as SeverityCode UNION
          SELECT 2 as SeverityCode UNION
          SELECT 1 as SeverityCode UNION
          SELECT 0 as SeverityCode
        ) AS SeverityCodes LEFT OUTER JOIN
        (
            SELECT MeterID, SeverityCode, COUNT(*) AS DisturbanceCount
            FROM DisturbanceSeverity JOIN
                 Disturbance ON Disturbance.ID = DisturbanceSeverity.DisturbanceID Join
                 Event ON Event.ID = Disturbance.EventID JOIN
                 VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID
            Where ( (CAST( Event.StartTime as Date) between @EventDateFrom and @EventDateTo))
            and Disturbance.PhaseID = (SELECT ID FROM Phase WHERE Name = 'Worst')
            and VoltageEnvelope.Name = COALESCE(@voltageEnvelope, 'ITIC')
            GROUP BY SeverityCode, MeterID
        ) AS Disturbances ON #temp.thesiteid = Disturbances.MeterID AND Disturbances.SeverityCode = SeverityCodes.SeverityCode
) AS DisturbanceData
PIVOT
(
    SUM(DisturbanceData.DisturbanceCount)
    FOR DisturbanceData.SeverityCode IN ([5], [4], [3], [2], [1], [0])
) as pvt
ORDER BY sitename asc

DROP TABLE #temp

END
GO

ALTER PROCEDURE [dbo].[selectDisturbancesForMeterIDByDateRange]

    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

DECLARE @dateStatement NVARCHAR(200) = N'CAST(Disturbance.StartTime AS Date)'
DECLARE @groupByStatement NVARCHAR(200) = N'CAST(Disturbance.StartTime AS Date)'

IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
    SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,Disturbance.StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(HOUR, Disturbance.StartTime), DateAdd(HOUR,DatePart(HOUR,Disturbance.StartTime), @EventDateFrom)'
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
    SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,Disturbance.StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(MINUTE, Disturbance.StartTime), DateAdd(MINUTE,DatePart(MINUTE,Disturbance.StartTime), @EventDateFrom)'
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
    SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,Disturbance.StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(SECOND, Disturbance.StartTime), DateAdd(SECOND,DatePart(SECOND,Disturbance.StartTime), @EventDateFrom)'
END

DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope')


DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''

create table #TEMP (Name varchar(max))
insert into #TEMP SELECT SeverityCode FROM (Select Distinct SeverityCode FROM DisturbanceSeverity) as t

SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(5)), '') + '],'
FROM #TEMP WHERE Name != 0 ORDER BY Name desc
SET @PivotColumns = @PivotColumns + '[0]'

SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(5)), '0') + '], 0) AS [' + COALESCE(CAST(Name as varchar(5)), '') + '],'
FROM #TEMP WHERE Name != 0 ORDER BY Name desc
SET @ReturnColumns = @ReturnColumns + 'COALESCE([0],0) as [0]'

SET @SQLStatement =
N'
DECLARE @user varchar(max) = @username
DECLARE @ids varchar(max) = @MeterID
DECLARE @start DateTime = @startDate
DECLARE @end DateTime = @endDate

SELECT *
INTO #selectedMeters
FROM String_To_Int_Table(@ids, '','')

SELECT DisturbanceDate as thedate, ' + @ReturnColumns + '
 FROM (
    SELECT
        ' + @dateStatement + ' AS DisturbanceDate,
        SeverityCode,
        COUNT(*) AS DisturbanceCount
    FROM
        DisturbanceSeverity JOIN
        Disturbance ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN
        Event ON Event.ID = Disturbance.EventID JOIN
        Phase ON Disturbance.PhaseID = Phase.ID JOIN
        VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID
    WHERE
        (
            @MeterID = ''0'' OR
            MeterID IN (SELECT * FROM #selectedMeters)
        ) AND
        Phase.Name = ''Worst'' AND
        VoltageEnvelope.Name = COALESCE(@voltageEnvelope, ''ITIC'') AND
        Disturbance.StartTime BETWEEN @start AND @end AND
        Disturbance.StartTime <> @endDate
    GROUP BY ' + @groupByStatement + ', SeverityCode
    ) As DisturbanceDate
 PIVOT(
        SUM(DisturbanceDate.DisturbanceCount)
        FOR DisturbanceDate.SeverityCode IN(' + @PivotColumns + ')
 ) as pvt
 ORDER BY DisturbanceDate '
print @sqlstatement
exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIMe, @endDate DATEtime, @EventDateFrom DATETIME, @voltageEnvelope VARCHAR(MAX)', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom, @voltageEnvelope = @voltageEnvelope

END
GO

ALTER PROCEDURE [dbo].[selectEventsForCalendar]
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

DECLARE @counter INT = 0
DECLARE @eventDate DATE = (Select max(CAST(StartTime AS Date)) from Event) --'2015-03-23'
DECLARE @numberOfDays INT = DATEDIFF ( day , (Select min(CAST(StartTime AS Date)) from Event), @eventDate) --365*5

SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate)

CREATE TABLE #temp(Date DATE)

WHILE (@counter <= @numberOfDays)
BEGIN
    INSERT INTO #temp VALUES(@eventDate)
    SET @eventDate = DATEADD(DAY, 1, @eventDate)
    SET @counter = @counter + 1
END

SELECT Date as thedate, Fault as faults, Interruption as interruptions, Sag as sags, Swell as swells, Other as others
FROM
(
    SELECT #temp.Date, EventType.Name AS EventTypeName, COALESCE(EventCount, 0) AS EventCount
    FROM
        #temp CROSS JOIN
        EventType LEFT OUTER JOIN
        (
            SELECT CAST(StartTime AS Date) AS EventDate, EventTypeID, COUNT(*) AS EventCount
            FROM Event 
            GROUP BY CAST(StartTime AS Date), EventTypeID
        ) AS Event ON #temp.Date = Event.EventDate AND EventType.ID = Event.EventTypeID
) AS EventDate
PIVOT
(
    SUM(EventCount)
    FOR EventDate.EventTypeName IN (Fault, Interruption, Sag, Swell, Other)
) as pvt
ORDER BY Date

DROP TABLE #temp

END
GO

ALTER PROCEDURE [dbo].[selectEventsForMeterIDByDateRange]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)

AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

DECLARE @dateStatement NVARCHAR(200) = N'CAST(StartTime AS Date)'
DECLARE @groupByStatement NVARCHAR(200) = N'CAST(StartTime AS Date)'

IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
    SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(HOUR, StartTime), DateAdd(HOUR,DatePart(HOUR,StartTime), @EventDateFrom)'
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
    SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(MINUTE, StartTime), DateAdd(MINUTE,DatePart(MINUTE,StartTime), @EventDateFrom)'
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
    SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(SECOND, StartTime), DateAdd(SECOND,DatePart(SECOND,StartTime), @EventDateFrom)'
END



DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''

SELECT @PivotColumns = @PivotColumns + '[' + t.Name + '],'
FROM (Select Name FROM EventType) AS t

SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.Name + '], 0) AS [' + t.Name + '],'
FROM (Select Name FROM EventType) AS t

SET @SQLStatement =
'
DECLARE @user varchar(max) = @username
DECLARE @ids varchar(max) = @MeterID
DECLARE @start DateTime = @startDate
DECLARE @end DateTime = @endDate

SELECT *
INTO #selectedMeters
FROM String_To_Int_Table(@ids, '','')

SELECT Date as thedate, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
FROM (
        SELECT ' + @dateStatement + ' as Date, COUNT(*) AS EventCount, EventType.Name as Name
        FROM Event JOIN
        EventType ON Event.EventTypeID = EventType.ID
       WHERE
            MeterID IN (SELECT * FROM #selectedMeters) AND
             StartTime >= @start AND StartTime < @end
       GROUP BY ' + @groupByStatement + ', EventType.Name
       ) as ed
 PIVOT(
        SUM(ed.EventCount)
        FOR ed.Name IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
 ) as pvt
 ORDER BY Date '

--print @startDate
--print @endDate
print @sqlstatement

exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom


END
GO

ALTER PROCEDURE [dbo].[selectEventsForMeterIDsByDate]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

declare  @MeterIDs TABLE (ID int);
INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',');

DECLARE @counter INT = 0
DECLARE @eventDate DATE = CAST(@EventDateTo AS Date)
DECLARE @numberOfDays INT = DATEDIFF ( day , CAST(@EventDateFrom AS Date) , @eventDate)

SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate)

CREATE TABLE #temp (thesiteid int, thesitename varchar(100))

INSERT INTO #temp Select [dbo].[Meter].[ID], [dbo].[Meter].[Name] from [dbo].[Meter] where Meter.ID in (Select * from @MeterIDs)

SELECT thesiteid as siteid, thesitename as sitename , Fault as faults, Interruption as interruptions, Sag as sags, Swell as swells, Other as others, Transient as transients
FROM
(
    SELECT #temp.thesiteid, #temp.thesitename , EventType.Name AS EventTypeName, COALESCE(EventCount, 0) AS EventCount
    FROM
        #temp CROSS JOIN
        EventType LEFT OUTER JOIN
        (
            SELECT MeterID, EventTypeID, COUNT(*) AS EventCount
            FROM Event
            WHERE (CAST([StartTime] as Date) between @EventDateFrom and @EventDateTo)
            GROUP BY EventTypeID, MeterID
        ) AS E ON EventType.ID = E.EventTypeID and E.MeterID = #temp.thesiteid
) AS EventDate
PIVOT
(
    SUM(EventCount)
    FOR EventDate.EventTypeName IN (Fault, Interruption, Sag, Swell, Other, Transient)
) as pvt
ORDER BY sitename asc

DROP TABLE #temp

END
GO

ALTER PROCEDURE [dbo].[selectFaultsForMeterIDByDateRange]
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

DECLARE @dateStatement NVARCHAR(200) = N'CAST(Event.StartTime AS Date)'
DECLARE @groupByStatement NVARCHAR(200) = N'CAST(Event.StartTime AS Date)'

IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
    SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,Event.StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(HOUR, Event.StartTime), DateAdd(HOUR,DatePart(HOUR,Event.StartTime), @EventDateFrom)'
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
    SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,Event.StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(MINUTE, Event.StartTime), DateAdd(MINUTE,DatePart(MINUTE,Event.StartTime), @EventDateFrom)'
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
    SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,Event.StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(SECOND, Event.StartTime), DateAdd(SECOND,DatePart(SECOND,Event.StartTime), @EventDateFrom)'
END



DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''

SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],'
FROM (Select Distinct Line.VoltageKV
        FROM Line) AS t

SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],'
FROM (Select Distinct Line.VoltageKV
        FROM Line) AS t

SET @SQLStatement =
' SELECT * ' +
' INTO #selectedMeters ' +
' FROM String_To_Int_Table(@MeterID, '','') ' +
' ' +
' SELECT Date as thedate, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) +
' FROM ( ' +
'       SELECT ' + @dateStatement + ' AS Date, Line.VoltageKV, COUNT(*) AS thecount ' +
'       FROM Event JOIN '+
'       EventType ON Event.EventTypeID = EventType.ID JOIN ' +
'       Line ON Event.LineID = Line.ID ' +
'       WHERE EventType.Name = ''Fault'' AND ' +
'       MeterID IN (SELECT * FROM #selectedMeters) AND Event.StartTime >= @startDate AND Event.StartTime < @endDate  ' +
'       GROUP BY ' + @groupByStatement + ', EventType.Name, Line.VoltageKV ' +
'       ) as eventtable ' +
' PIVOT( ' +
'       SUM(eventtable.thecount) ' +
'       FOR eventtable.VoltageKV IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') ' +
' ) as pvt ' +
' ORDER BY Date '

exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DateTime ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom
END
GO

ALTER PROCEDURE [dbo].[selectFaultsForMeterIDsByDate]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)

AS
BEGIN

    SET NOCOUNT ON;

declare  @MeterIDs TABLE (ID int);

INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',');

DECLARE @counter INT = 0
DECLARE @eventDate DATE = CAST(@EventDateTo AS Date)
DECLARE @numberOfDays INT = DATEDIFF ( day , CAST(@EventDateFrom AS Date) , @eventDate)

SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate)

CREATE TABLE #temp (thesiteid int, thesitename varchar(100))

INSERT INTO #temp Select [dbo].[Meter].[ID], [dbo].[Meter].[Name] from [dbo].[Meter] where [dbo].[Meter].[ID] in (Select * from @MeterIDs)

SELECT thesiteid as siteid, thesitename as sitename , Fault as faults
FROM
(
    SELECT #temp.thesiteid, #temp.thesitename , EventType.Name AS EventTypeName, COALESCE(EventCount, 0) AS EventCount
    FROM
        #temp CROSS JOIN
        EventType LEFT OUTER JOIN
        (
            SELECT MeterID, EventTypeID, COUNT(*) AS EventCount FROM Event
            where MeterID in (Select * from @MeterIDs)
            and (CAST([StartTime] as Date) between @EventDateFrom and @EventDateTo)

            GROUP BY EventTypeID, MeterID
        ) AS E ON EventType.ID = E.EventTypeID and E.MeterID = #temp.thesiteid and EventType.Name = 'Fault'
) AS EventDate
PIVOT
(
    SUM(EventCount)
    FOR EventDate.EventTypeName IN (Fault, Interruption, Sag, Swell, Other)
) as pvt
ORDER BY sitename asc

DROP TABLE #temp

END
GO

ALTER PROCEDURE [dbo].[selectExtensionsForMeterIDByDateRange]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)

AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

DECLARE @dateStatement NVARCHAR(200) = N'CAST(StartTime AS Date)'
DECLARE @groupByStatement NVARCHAR(200) = N'CAST(StartTime AS Date)'

IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
    SET @dateStatement = N'DateAdd(HOUR,DatePart(HOUR,StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(HOUR, StartTime), DateAdd(HOUR,DatePart(HOUR,StartTime), @EventDateFrom)'
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
    SET @dateStatement = N'DateAdd(MINUTE,DatePart(MINUTE,StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(MINUTE, StartTime), DateAdd(MINUTE,DatePart(MINUTE,StartTime), @EventDateFrom)'
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
    SET @dateStatement = N'DateAdd(SECOND,DatePart(SECOND,StartTime), @EventDateFrom)'
    SET @groupByStatement = N'DATEPART(SECOND, StartTime), DateAdd(SECOND,DatePart(SECOND,StartTime), @EventDateFrom)'
END



DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @MiddleStatment NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''

SELECT @PivotColumns = @PivotColumns + '[' + t.ServiceName + '],'
FROM (Select ServiceName FROM EASExtension) AS t

SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.ServiceName + '], 0) AS [' + t.ServiceName + '],'
FROM (Select ServiceName FROM EASExtension) AS t

SELECT @MiddleStatment = @MiddleStatment +  '
            SELECT '+@dateStatement+' as date,
                '''+ t.ServiceName + ''' as ServiceName,
                Count(*) as EventCount
            FROM    #temp
            WHERE   dbo.' + t.HasResultFunction + '(ID) != ''''
            GROUP BY '+ @dateStatement + ' UNION'

FROM (Select * FROM EASExtension) AS t

SELECT * INTO #temp
FROM EVENT
WHERE   StartTime Between @startDate AND @endDate AND
        MeterID IN (SELECT * FROM String_To_Int_Table( @MeterID,  ','))

CREATE INDEX tempIndex ON #temp (MeterID)

SET @SQLStatement =
'
SELECT Date as thedate, ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
FROM (
        SELECT * FROM (
        ' + SUBSTRING(@MiddleStatment,0, LEN(@MiddleStatment) - LEN('UNION')) + ' ) as innertable
       ) as ed
 PIVOT(
        SUM(ed.EventCount)
        FOR ed.ServiceName IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
 ) as pvt
 ORDER BY Date

 DROP TABLE #temp
 '

--print @startDate
--print @endDate
print @sqlstatement

exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom

END
GO

ALTER PROCEDURE [dbo].[selectHeatmapMeterLocationsTrending]
    -- Add the parameters for the stored procedure here
    @EventDate DateTime2,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

declare  @MeterIDs TABLE (ID int);
INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',');

DECLARE @ChannelList VARCHAR(MAX)

--SELECT @ChannelList = CAST([dbo].[Channel].[ID] as Varchar(Max)) + ',' + @ChannelList

SELECT @ChannelList = Coalesce(@ChannelList+ ',','') + CAST([dbo].[Channel].[ID] as Varchar(Max))

  FROM [dbo].[Channel] join [dbo].[Meter] on [dbo].[Meter].[ID] = [dbo].[Channel].[MeterID] and [dbo].[Meter].[ID] in (Select ID from @MeterIDs)
  where [dbo].[Channel].[MeasurementCharacteristicID] =
  (
  SELECT [ID]
  FROM [dbo].[MeasurementCharacteristic] where Name = 'TotalTHD'
  )
  and
  [dbo].[Channel].[PhaseID] =
  (
  SELECT [ID]
  FROM [dbo].[Phase] where Name = 'AN'
  )

  --Print @ChannelList

BEGIN

WITH TrendingDataPoint AS
(
    SELECT
        ChannelID,
        SeriesType,
        Time,
        Value
    FROM
        GetTrendingData(@EventDate, DATEADD(NANOSECOND, -100, DATEADD(minute, 5, @EventDate)), @ChannelList, default) AS TrendingData JOIN
        (
            SELECT 0 AS ID, 'Minimum' AS SeriesType UNION
            SELECT 1 AS ID, 'Maximum' AS SeriesType UNION
            SELECT 2 AS ID, 'Average' AS SeriesType
        ) AS Series ON TrendingData.SeriesID = Series.ID
),
TrendingData AS
(
    SELECT
        ChannelID,
        Time,
        Maximum,
        Minimum,
        Average
    FROM TrendingDataPoint
    PIVOT
    (
        SUM(Value)
        FOR SeriesType IN (Maximum, Minimum, Average)
    ) AS TrendingData
)
SELECT
Coalesce([dbo].[Meter].[ID],0) as MeterID,
[dbo].[MeterLocation].[Longitude] as Longitude,
[dbo].[MeterLocation].[Latitude] as Latitude,
[TrendingData].[Time] as thedate,
Cast(Round(([TrendingData].[Maximum] * 100),0) as Int) as Value

FROM [TrendingData]
    left outer join [dbo].[AlarmRangeLimit] on [dbo].[AlarmRangeLimit].[ChannelID] = [TrendingData].[ChannelID]
    left outer join [dbo].[HourOfWeekLimit] on [dbo].[HourOfWeekLimit].[ChannelID] = [TrendingData].[ChannelID]
    join [dbo].[Channel] on [dbo].[Channel].[ID] = TrendingData.ChannelID
    join [dbo].[Meter] on [dbo].[Meter].[ID] = [dbo].[Channel].[MeterID]
    join [dbo].[Meterlocation] on [dbo].[Meter].[MeterLocationID] = [dbo].[MeterLocation].[ID]
  where
      ([dbo].[HourOfWeekLimit].[HourOfWeek] = 0 or [dbo].[HourOfWeekLimit].[HourOfWeek] is null)
    order by [TrendingData].[Time]

END

END
GO

ALTER PROCEDURE [dbo].[selectMeterIDsForArea]
    -- Add the parameters for the stored procedure here
    @ax as float,
    @ay as float,
    @bx as float,
    @by as float,
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

        SELECT distinct [dbo].[Meter].[ID] as TheMeterID from [dbo].[Meter] inner join [dbo].[MeterLocation] on [dbo].[Meter].[MeterLocationID] = [dbo].[MeterLocation].[ID]

        where ( [dbo].[MeterLocation].[Latitude] between @by and @ay and [dbo].[MeterLocation].[Longitude] between @ax and @bx )

END
GO

ALTER PROCEDURE [dbo].[selectMeterLocationsBreakers]
    @EventDateFrom DATETIME,
    @EventDateTo DATETIME,
    @meterIds AS varchar(max),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @startDate DATETIME = @EventDateFrom
    DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))


    IF @context = 'day'
    BEGIN
        SET @endDate = DATEADD(DAY, 1, @startDate)
    END

    if @context = 'hour'
    BEGIN
        SET @endDate = DATEADD(HOUR, 1, @startDate)
    END

    if @context = 'minute'
    BEGIN
        SET @endDate = DATEADD(MINUTE, 1, @startDate)
    END

    if @context = 'second'
    BEGIN
        SET @endDate = DATEADD(SECOND, 1, @startDate)
    END

    DECLARE @PivotColumns NVARCHAR(MAX) = N''
    DECLARE @CountColumns NVARCHAR(MAX) = N''
    DECLARE @ReturnColumns NVARCHAR(MAX) = N''
    DECLARE @SQLStatement NVARCHAR(MAX) = N''

    create table #TEMP (Name varchar(max))
    insert into #TEMP SELECT Name FROM (Select Distinct Name FROM BreakerOperationType) as t

    SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(max)), '') + '],'
    FROM #TEMP ORDER BY Name desc

    SELECT @CountColumns = @CountColumns + 'COALESCE([' + COALESCE(CAST(Name as varchar(20)), '') + '], 0) + '
    FROM #TEMP ORDER BY Name desc

    SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(max)), '') + '],'
    FROM #TEMP ORDER BY Name desc

    DROP TABLE #TEMP


    SET @SQLStatement =
'   SELECT *
    INTO #selectedMeters
    FROM String_To_Int_Table(@MeterIds, '','')

    SELECT Meter.ID,
             Meter.Name,
             MeterLocation.Longitude,
             MeterLocation.Latitude,
             ' + SUBSTRING(@CountColumns,0, LEN(@CountColumns)) +' as Count,
             ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
    FROM
        Meter JOIN
        MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN
        (
            SELECT MeterID,
                   COUNT(*) AS EventCount,
                   BreakerOperationType.Name as OperationType
            FROM BreakerOperation JOIN
                 Event ON BreakerOperation.EventID = Event.ID JOIN
                 EventType ON Event.EventTypeID = EventType.ID JOIN
                 BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID
            WHERE
                 TripCoilEnergized >= @startDate AND TripCoilEnergized < @endDate
            GROUP BY Event.MeterID, BreakerOperationType.Name
           ) as ed
           PIVOT(
                 SUM(ed.EventCount)
                 FOR ed.OperationType IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
           ) as pvt On pvt.MeterID = meter.ID
    WHERE
        Meter.ID IN (SELECT * FROM #selectedMeters)

    ORDER BY Meter.Name'

    print @SQLStatement
    exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom

END
GO

ALTER PROCEDURE [dbo].[selectMeterLocationsDisturbances]

    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @meterIds AS varchar(max),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))


IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
END

if @context = 'second'
BEGIN
    SET @endDate = DATEADD(SECOND, 1, @startDate)
END


DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @CountColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''


create table #TEMP (Name varchar(max))
insert into #TEMP SELECT SeverityCode FROM (Select Distinct SeverityCode FROM DisturbanceSeverity) as t

SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(5)), '') + '],'
FROM #TEMP WHERE Name != 0 ORDER BY Name desc
SET @PivotColumns = @PivotColumns + '[0]'

SELECT @CountColumns = @CountColumns + 'COALESCE([' + COALESCE(CAST(Name as varchar(5)), '') + '], 0) + '
FROM #TEMP WHERE Name != 0 ORDER BY Name desc
SET @CountColumns = @CountColumns + 'COALESCE([0], 0) '


SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(5)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(5)), '') + '],'
FROM #TEMP WHERE Name != 0ORDER BY Name desc
SET @ReturnColumns = @ReturnColumns + 'COALESCE([0], 0) as [0]'

DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope')

DROP TABLE #TEMP

SET @SQLStatement = N'
DECLARE @user varchar(max) = @username
DECLARE @ids varchar(max) = @MeterIds
DECLARE @start DateTime = @startDate
DECLARE @end DateTime = @endDate

SELECT *
INTO #selectedMeters
FROM String_To_Int_Table(@ids, '','')

SELECT Meter.ID,
         Meter.Name,
         MeterLocation.Longitude,
         MeterLocation.Latitude,
         ' + @CountColumns +' as Count,
         ' + @ReturnColumns + '
FROM
    Meter JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN
    (
        SELECT MeterID,
               COUNT(*) AS EventCount,
               SeverityCode
        FROM Event JOIN
             Disturbance ON Event.ID = Disturbance.EventID JOIN
             Phase ON Phase.ID = Disturbance.PhaseID LEFT JOIN
             DisturbanceSeverity ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN
             VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID
        WHERE
             Phase.Name = ''Worst'' AND
             Disturbance.StartTime >= @start AND Disturbance.StartTime < @end AND
             VoltageEnvelope.Name = COALESCE(@voltageEnvelope, ''ITIC'')
        GROUP BY Event.MeterID, SeverityCode
       ) as ed
       PIVOT(
             SUM(ed.EventCount)
             FOR ed.SeverityCode IN(' + @PivotColumns + ')
       ) as pvt On pvt.MeterID = meter.ID
    WHERE
        Meter.ID IN (SELECT * FROM #selectedMeters)

Order By Name '

print @SqlStatement
exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME , @voltageEnvelope VARCHAR(MAX)', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate, @voltageEnvelope = @voltageEnvelope

END
GO

ALTER PROCEDURE [dbo].[selectMeterLocationsEvents]
    @EventDateFrom DATETIME,
    @EventDateTo DATETIME,
    @meterIds AS varchar(max),
    @username as nvarchar(4000),
    @context as nvarchar(20)

AS
BEGIN

    SET NOCOUNT ON;
DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))


IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
END

if @context = 'second'
BEGIN
    SET @endDate = DATEADD(SECOND, 1, @startDate)
END


DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @CountColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''

SELECT @PivotColumns = @PivotColumns + '[' + t.Name + '],'
FROM (Select Name FROM EventType) AS t

SELECT @CountColumns = @CountColumns + 'COALESCE([' + t.Name + '], 0) + '
FROM (Select Name FROM EventType) AS t


SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.Name + '], 0) AS [' + t.Name + '],'
FROM (Select Name FROM EventType) AS t

SET @SQLStatement =
'
DECLARE @user varchar(max) = @username
DECLARE @ids varchar(max) = @MeterIds
DECLARE @start DateTime = @startDate
DECLARE @end DateTime = @endDate

SELECT *
INTO #selectedMeters
FROM String_To_Int_Table(@ids, '','')

SELECT Meter.ID,
         Meter.Name,
         MeterLocation.Longitude,
         MeterLocation.Latitude,
         ' + SUBSTRING(@CountColumns,0, LEN(@CountColumns)) +' as Count,
         ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
FROM
    Meter JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN
    (
        SELECT MeterID,
               COUNT(*) AS EventCount,
               EventType.Name as Name

        FROM Event JOIN
             EventType ON Event.EventTypeID = EventType.ID
        WHERE
             StartTime >= @start AND StartTime < @end
        GROUP BY Event.MeterID, EventType.Name
       ) as ed
       PIVOT(
             SUM(ed.EventCount)
             FOR ed.Name IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
       ) as pvt On pvt.MeterID = meter.ID
WHERE
    Meter.ID IN (SELECT * FROM #selectedMeters)

ORDER BY Meter.Name'

--print @startDate
--print @endDate
print @sqlstatement

exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate
END
GO

ALTER PROCEDURE [dbo].[selectMeterLocationsFaults]
    @EventDateFrom DateTime,
    @EventDateTo DateTime,
    @meterIds AS varchar(max),
    @username as nvarchar(4000),
    @context as nvarchar(20)

AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))


IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
END

if @context = 'second'
BEGIN
    SET @endDate = DATEADD(SECOND, 1, @startDate)
END


DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @CountColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''

SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],'
FROM (Select Distinct Line.VoltageKV
        FROM Line) AS t

SELECT @CountColumns = @CountColumns + 'COALESCE([' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '], 0) + '
FROM (Select Distinct Line.VoltageKV
        FROM Line) AS t

SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(t.VoltageKV as varchar(max)), '') + '],'
FROM (Select Distinct Line.VoltageKV
        FROM Line) AS t


SET @SQLStatement =
'SELECT *
INTO #selectedMeters
FROM String_To_Int_Table(@MeterIds, '','')

SELECT Meter.ID,
         Meter.Name,
         MeterLocation.Longitude,
         MeterLocation.Latitude,
         ' + SUBSTRING(@CountColumns,0, LEN(@CountColumns)) +' as Count,
         ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
FROM
    Meter JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN
    (
        SELECT MeterID,
               COUNT(*) AS EventCount,
               VoltageKV
        FROM Event JOIN
             EventType ON Event.EventTypeID = EventType.ID JOIN
             Line ON Event.LineID = Line.ID
        WHERE
             StartTime >= @startDate AND StartTime < @endDate AND
             EventType.Name = ''Fault''
        GROUP BY Event.MeterID, VoltageKV
       ) as ed
       PIVOT(
             SUM(ed.EventCount)
             FOR ed.VoltageKV IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
       ) as pvt On pvt.MeterID = meter.ID
    WHERE
        Meter.ID IN (SELECT * FROM #selectedMeters)

ORDER BY Meter.Name'

print @SQLStatement
exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom

END
GO

ALTER PROCEDURE [dbo].[selectMeterLocationsExtensions]
    @EventDateFrom DATETIME,
    @EventDateTo DATETIME,
    @meterIds AS varchar(max),
    @username as nvarchar(4000),
    @context as nvarchar(20)

AS
BEGIN

    SET NOCOUNT ON;
DECLARE @startDate DATETIME = @EventDateFrom
DECLARE @endDate DATETIME = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))


IF @context = 'day'
BEGIN
    SET @endDate = DATEADD(DAY, 1, @startDate)
END

if @context = 'hour'
BEGIN
    SET @endDate = DATEADD(HOUR, 1, @startDate)
END

if @context = 'minute'
BEGIN
    SET @endDate = DATEADD(MINUTE, 1, @startDate)
END

if @context = 'second'
BEGIN
    SET @endDate = DATEADD(SECOND, 1, @startDate)
END


DECLARE @PivotColumns NVARCHAR(MAX) = N''
DECLARE @CountColumns NVARCHAR(MAX) = N''
DECLARE @ReturnColumns NVARCHAR(MAX) = N''
DECLARE @SQLStatement NVARCHAR(MAX) = N''
DECLARE @MiddleStatment NVARCHAR(MAX) = N''

SELECT * INTO #temp
FROM EVENT
WHERE   StartTime Between @startDate AND @endDate AND
        MeterID IN (SELECT * FROM String_To_Int_Table( @meterIds,  ','))

CREATE INDEX tempIndex ON #temp (MeterID)

CREATE TABLE #easTable( MeterID int, ServiceName varchar(max), EventCount int);

SELECT @PivotColumns = @PivotColumns + '[' + t.ServiceName + '],'
FROM (Select ServiceName FROM EASExtension) AS t

SELECT @CountColumns = @CountColumns + 'COALESCE([' + t.ServiceName + '], 0) + '
FROM (Select ServiceName FROM EASExtension) AS t


SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.ServiceName + '], 0) AS [' + t.ServiceName + '],'
FROM (Select ServiceName FROM EASExtension) AS t

DECLARE @serviceName as varchar(max);
DECLARE @hasResultFunction as varchar(max);

DECLARE aCursor CURSOR FOR
SELECT ServiceName, HasResultFunction FROM EASExtension
OPEN aCursor;
FETCH NEXT FROM aCursor into @serviceName, @hasResultFunction;
WHILE @@FETCH_STATUS = 0
   BEGIN
    DECLARE @Sql nvarchar(max) = N'
            INSERT INTO #easTable
            SELECT MeterID,
                '''+ @serviceName + ''' as ServiceName,
                Count(*) as EventCount
            FROM    #temp
            WHERE   dbo.' + @hasResultFunction + '(ID) != ''''
            GROUP BY MeterID'
    exec sp_executesql @sql
    FETCH NEXT FROM aCursor into @serviceName, @hasResultFunction;
   END;
CLOSE aCursor;
DEALLOCATE aCursor;


SELECT @MiddleStatment = @MiddleStatment +  '
            SELECT MeterID,
                '''+ t.ServiceName + ''' as ServiceName,
                Count(*) as EventCount
            FROM    #temp
            WHERE   dbo.' + t.HasResultFunction + '(ID) != ''''
            GROUP BY MeterID UNION'

FROM (Select * FROM EASExtension) AS t

SET @SQLStatement =
'SELECT *
INTO #selectedMeters
FROM String_To_Int_Table(@MeterIds, '','')

SELECT Meter.ID,
         Meter.Name,
         MeterLocation.Longitude,
         MeterLocation.Latitude,
         ' + SUBSTRING(@CountColumns,0, LEN(@CountColumns)) +' as Count,
         ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
FROM
    Meter JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN
    #easTable as ed
       PIVOT(
             SUM(ed.EventCount)
             FOR ed.ServiceName IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
       ) as pvt On pvt.MeterID = meter.ID
WHERE
    Meter.ID IN (SELECT * FROM #selectedMeters)

ORDER BY Meter.Name

DROP TABLE #temp
DROP TABLE #easTable
'
--print @startDate
--print @endDate
print @sqlstatement

exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterIds nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @EventDateFrom DATETIME ', @username = @username, @MeterIds = @MeterIds, @startDate = @startDate, @endDate = @endDate, @EventDateFrom = @EventDateFrom

END

GO

ALTER PROCEDURE [dbo].[selectMeters]
        @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;
        SELECT distinct [dbo].[Meter].[ID] as id, [dbo].[Meter].[Name]
        from [dbo].[Meter] 
        order by [dbo].[Meter].[Name]

END
GO

ALTER PROCEDURE [dbo].[selectPowerLineClasses]
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

select distinct VoltageKV as class from [dbo].[Line]
join Channel on Line.ID = Channel.LineID
join Meter on Channel.MeterID = Meter.ID

order by VoltageKV Desc

END
GO

ALTER PROCEDURE [dbo].[selectSitesBreakersDetailsByDate]
    @EventDate AS DATETIME,
    @MeterID AS NVARCHAR(MAX),
    @username AS NVARCHAR(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime
    DECLARE @endDate DateTime

    IF @context = 'day'
    BEGIN
        SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @endDate = DATEADD(DAY, 1, @startDate)
    END

    if @context = 'hour'
    BEGIN
        SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0)
        SET @endDate = DATEADD(HOUR, 1, @startDate)
    END

    if @context = 'minute'
    BEGIN
        SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0)
        SET @endDate = DATEADD(MINUTE, 1, @startDate)
    END

    if @context = 'second'
    BEGIN
        DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate)
        SET @endDate = DATEADD(SECOND, 1, @startDate)
    END

    SELECT
        Meter.ID AS meterid,
        Event.ID AS theeventid,
        EventType.Name AS eventtype,
        BreakerOperation.ID AS breakeroperationid,
        CAST(CAST(BreakerOperation.TripCoilEnergized AS TIME) AS NVARCHAR(100)) AS energized,
        BreakerOperation.BreakerNumber AS breakernumber,
        MeterLine.LineName AS linename,
        Phase.Name AS phasename,
        CAST(BreakerOperation.BreakerTiming AS DECIMAL(16,5)) AS timing,
        CAST(BreakerOperation.StatusTiming AS DECIMAL(16,5)) AS statustiming,
        BreakerOperation.BreakerSpeed AS speed,
        BreakerOperation.StatusBitChatter AS chatter,
        BreakerOperation.DcOffsetDetected AS dcoffset,
        BreakerOperationType.Name AS operationtype,
        (SELECT COUNT(*) FROM EventNote WHERE EventNote.EventID = Event.ID) as notecount
    FROM
        BreakerOperation JOIN
        Event ON BreakerOperation.EventID = Event.ID JOIN
        EventType ON EventType.ID = Event.EventTypeID JOIN
        Meter ON Meter.ID = Event.MeterID JOIN
        Line ON Line.ID = Event.LineID JOIN
        MeterLine ON MeterLine.LineID = Event.LineID AND MeterLine.MeterID = Meter.ID JOIN
        BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID JOIN
        Phase ON BreakerOperation.PhaseID = Phase.ID
    WHERE
        TripCoilEnergized >= @startDate AND TripCoilEnergized < @endDate AND
        Meter.ID IN (SELECT * FROM  dbo.String_to_int_table(@MeterID, ','))
END
GO

ALTER PROCEDURE [dbo].[selectSitesCompletenessDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate AS DATETIME,
    @MeterID AS NVARCHAR(MAX),
    @username AS NVARCHAR(4000)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @thedate DATE = CAST(@EventDate AS DATE)
    DECLARE @MeterIDs TABLE (ID INT);

    INSERT INTO @MeterIDs(ID)
    SELECT Value FROM dbo.String_to_int_table(@MeterID, ',')

    DECLARE @TempTable TABLE (themeterid INT, thesite VARCHAR(100), thecount FLOAT, thename VARCHAR(100));

    INSERT INTO @TempTable (themeterid, thesite , thecount , thename)
    SELECT
        Meter.ID AS meterid,
        Meter.Name AS thesite,
        (
            SELECT COALESCE((
                SELECT CAST(CAST((GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints) AS FLOAT) / NULLIF(CAST(expectedPoints AS FLOAT), 0) AS FLOAT) AS completenessPercentage
                FROM MeterDataQualitySummary
                WHERE
                    MeterID = Meter.ID AND
                    [Date] = @thedate
            )
        , 0)) AS thecount,
        'Completeness' as thename
    FROM
        MeterDataQualitySummary JOIN
        Meter ON Meter.ID = MeterDataQualitySummary.MeterID
    WHERE
        MeterID IN (SELECT * FROM @MeterIDs) AND
        CAST([Date] AS DATE) = @thedate

    INSERT INTO @TempTable (themeterid, thesite , thecount , thename)
    SELECT
        Meter.ID AS meterid,
        Meter.Name AS thesite,
        (
            SELECT COALESCE((
                SELECT CAST(NULLIF(CAST(expectedPoints AS FLOAT), 0) AS FLOAT) AS completenessPercentage
                FROM MeterDataQualitySummary
                WHERE
                    MeterID = Meter.ID AND
                    [Date] = @thedate
            )
        , 0)) AS thecount,
        'Expected' AS thename
    FROM
        MeterDataQualitySummary JOIN
        Meter ON Meter.ID = MeterDataQualitySummary.MeterID
    WHERE
        MeterID IN (SELECT * FROM @MeterIDs) AND
        CAST([Date] AS DATE) = @thedate

    INSERT INTO @TempTable (themeterid, thesite , thecount , thename)
    SELECT
        Meter.ID AS meterid,
        Meter.Name AS thesite,
        (
            SELECT COALESCE((
                SELECT CAST(CAST((GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints + DuplicatePoints) AS FLOAT) / NULLIF(CAST(expectedPoints AS FLOAT), 0) AS FLOAT) AS completenessPercentage
                FROM MeterDataQualitySummary
                WHERE
                    MeterID = Meter.ID AND
                    [Date] = @theDate
            )
        , 0)) AS thecount,
        'Received' AS thename
    FROM
        MeterDataQualitySummary JOIN
        Meter ON Meter.ID = MeterDataQualitySummary.MeterID
    WHERE
        MeterID IN (SELECT * FROM @MeterIDs) AND
        CAST([Date] AS DATE) = @thedate

    INSERT INTO @TempTable (themeterid, thesite , thecount , thename)
    SELECT
        Meter.ID AS meterid,
        Meter.Name AS thesite,
        (
            SELECT COALESCE((
                SELECT CAST(CAST((DuplicatePoints) AS FLOAT) / NULLIF(CAST(expectedPoints AS FLOAT), 0) AS FLOAT) AS completenessPercentage
                FROM MeterDataQualitySummary
                WHERE
                    MeterID = Meter.ID AND
                    [Date] = @thedate
            )
        , 0)) AS thecount,
        'Duplicate' AS thename
    FROM
        MeterDataQualitySummary JOIN
        Meter ON Meter.ID = MeterDataQualitySummary.MeterID
    WHERE
        MeterID IN (SELECT * FROM @MeterIDs) AND
        CAST([Date] AS DATE) = @thedate

    DECLARE @composite TABLE (theeventid INT, themeterid INT, thesite VARCHAR(100), Expected FLOAT, Received FLOAT, Duplicate FLOAT, Completeness FLOAT);

    DECLARE @sitename VARCHAR(100)
    DECLARE @themeterid INT
    DECLARE @theeventid INT

    DECLARE site_cursor CURSOR FOR SELECT DISTINCT themeterid, thesite FROM @TempTable

    OPEN site_cursor

    FETCH NEXT FROM site_cursor INTO @themeterid , @sitename

    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT @composite VALUES(
            (
                SELECT TOP 1 MeterDataQualitySummary.ID
                FROM MeterDataQualitySummary
                WHERE
                    MeterDataQualitySummary.MeterID = @themeterid AND
                    CAST([Date] as Date) = @thedate
            ),
            @themeterid,
            @sitename,
            (SELECT thecount * 100 FROM @TempTable WHERE thename = 'Expected' AND thesite = @sitename),
            (SELECT thecount * 100 FROM @TempTable WHERE thename = 'Received' AND thesite = @sitename),
            (SELECT thecount * 100 FROM @TempTable WHERE thename = 'Duplicate' AND thesite = @sitename),
            (SELECT thecount * 100 FROM @TempTable WHERE thename = 'Completeness' AND thesite = @sitename)
        )

        FETCH NEXT FROM site_cursor INTO @themeterid , @sitename
    END

    CLOSE site_cursor;
    DEALLOCATE site_cursor;

    SELECT * FROM @composite
END
GO

ALTER PROCEDURE [dbo].[selectSitesCorrectnessDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @thedate DATE = CAST(@EventDate AS DATE)
    DECLARE @MeterIDs TABLE (ID INT);

    INSERT INTO @MeterIDs(ID)
    SELECT Value
    FROM dbo.String_to_int_table(@MeterID, ',')

    DECLARE @TempTable TABLE (themeterid INT, thesite VARCHAR(100), thecount FLOAT, thename VARCHAR(100));

    INSERT INTO @TempTable (themeterid, thesite , thecount , thename)
    SELECT
        Meter.ID AS meterid,
        Meter.Name AS thesite,
        (
            SELECT COALESCE(ROUND(CAST(SUM(LatchedPoints) AS FLOAT) / NULLIF(CAST(SUM(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints) AS FLOAT), 0) * 100 , 0), 0) AS correctnessPercentage
            FROM MeterDataQualitySummary
            WHERE
                MeterID = Meter.ID AND
                [Date] = @thedate
        ) AS thecount,
        'Latched' as thename
    FROM
        MeterDataQualitySummary JOIN
        Meter ON Meter.ID = MeterDataQualitySummary.MeterID
    WHERE
        MeterID IN (SELECT * FROM @MeterIDs) AND
        CAST([Date] AS DATE) = @thedate

    INSERT INTO @TempTable (themeterid, thesite , thecount , thename)
    SELECT
        Meter.ID AS meterid,
        Meter.Name AS thesite,
        (
            SELECT COALESCE(ROUND(CAST(SUM(UnreasonablePoints) AS FLOAT) / NULLIF(CAST(SUM(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints) AS FLOAT), 0) * 100 ,0), 0) AS correctnessPercentage
            FROM MeterDataQualitySummary
            WHERE
                MeterID = [dbo].[Meter].[ID] AND
                [Date] = @thedate
        ) AS thecount,
        'Unreasonable' AS thename
    FROM
        MeterDataQualitySummary JOIN
        Meter ON Meter.ID = MeterDataQualitySummary.MeterID
    WHERE
        MeterID IN (SELECT * FROM @MeterIDs) AND
        CAST([Date] AS DATE) = @thedate

    INSERT INTO @TempTable(themeterid, thesite , thecount , thename)
    SELECT
        Meter.ID AS meterid,
        Meter.Name AS thesite,
        (
            SELECT COALESCE(ROUND(CAST(SUM(NoncongruentPoints) AS FLOAT) / NULLIF(CAST(SUM(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints) AS FLOAT), 0) * 100 , 0), 0) AS correctnessPercentage
            FROM MeterDataQualitySummary
            WHERE
                MeterID = Meter.ID AND
                [Date] = @thedate
        ) AS thecount,
        'Noncongruent' AS thename
    FROM
        MeterDataQualitySummary JOIN
        Meter ON Meter.ID = MeterDataQualitySummary.MeterID
    WHERE
        MeterID IN (SELECT * FROM @MeterIDs) AND
        CAST([Date] AS DATE) = @thedate

    DECLARE @composite TABLE (theeventid INT, themeterid INT, thesite VARCHAR(100), Latched FLOAT, Unreasonable FLOAT, Noncongruent FLOAT, Correctness FLOAT);

    DECLARE @sitename VARCHAR(100)
    DECLARE @themeterid INT
    DECLARE @theeventid INT

    DECLARE site_cursor CURSOR FOR SELECT DISTINCT themeterid, thesite FROM @TempTable

    OPEN site_cursor

    FETCH NEXT FROM site_cursor INTO @themeterid, @sitename

    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO @composite VALUES(
            (
                SELECT TOP 1 MeterDataQualitySummary.ID
                FROM MeterDataQualitySummary
                WHERE
                    MeterDataQualitySummary.MeterID = @themeterid AND
                    CAST([Date] AS DATE) = @theDate
            ),
            @themeterid,
            @sitename,
            (SELECT thecount FROM @TempTable WHERE thename = 'Latched' AND thesite = @sitename),
            (SELECT thecount FROM @TempTable WHERE thename = 'Unreasonable' AND thesite = @sitename),
            (SELECT thecount FROM @TempTable WHERE thename = 'Noncongruent' AND thesite = @sitename),
             (
                    SELECT 100.0 * CAST(GoodPoints AS FLOAT) / CAST(NULLIF(GoodPoints + LatchedPoints + UnreasonablePoints + NoncongruentPoints, 0) AS FLOAT) AS Correctness
                    FROM MeterDataQualitySummary
                    WHERE CAST([Date] AS DATE) = @theDate AND MeterID = @themeterid
             )
        )

        FETCH NEXT FROM site_cursor INTO @themeterid , @sitename
    END

    CLOSE site_cursor;
    DEALLOCATE site_cursor;

    SELECT * FROM @composite
END
GO

ALTER PROCEDURE [dbo].[selectSitesDisturbancesDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime = @EventDate
    DECLARE @endDate DateTime

    if @context = 'day'
        SET @endDate = DATEADD(DAY, 1, @startDate);
    if @context = 'hour'
        SET @endDate = DATEADD(HOUR, 1, @startDate);
    if @context = 'minute'
        SET @endDate = DATEADD(MINUTE, 1, @startDate);
    if @context = 'second'
        SET @endDate = DATEADD(SECOND, 1, @startDate);

    DECLARE @PivotColumns NVARCHAR(MAX) = N''
    DECLARE @ReturnColumns NVARCHAR(MAX) = N''
    DECLARE @SQLStatement NVARCHAR(MAX) = N''

    create table #TEMP (Name varchar(max))
    insert into #TEMP SELECT SeverityCode FROM (Select Distinct SeverityCode FROM DisturbanceSeverity) as t

    SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(5)), '') + '],'
    FROM #TEMP ORDER BY Name desc

    SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(5)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(5)), '') + '],'
    FROM #TEMP ORDER BY Name desc
    DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope')

    SET @SQLStatement =
    'SELECT *
    INTO #meterSelections
    FROM String_To_Int_Table(@MeterID, '','')

    SELECT (SELECT TOP 1 ID FROM Event WHERE MeterID = pvt.MeterID AND StartTime >= @startDate AND StartTime < @endDate)
             EventID,
             MeterID,
             Site,
             ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
     FROM (
        SELECT
            Event.MeterID, COUNT(*) AS EventCount, SeverityCode, Meter.Name as Site
        FROM
            Disturbance JOIN
            DisturbanceSeverity ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN
            Event ON Disturbance.EventID = Event.ID JOIN
            EventType ON Event.EventTypeID = EventType.ID JOIN
            Meter ON Event.MeterID = Meter.ID JOIN
            Phase ON Phase.ID = Disturbance.PhaseID JOIN
            VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID
        WHERE
            Phase.Name = ''Worst'' AND
            MeterID IN (SELECT * FROM #meterSelections) AND
            VoltageEnvelope.Name = COALESCE(@voltageEnvelope, ''ITIC'') AND
            Event.StartTime >= @startDate AND Event.StartTime < @endDate
        GROUP BY Event.MeterID,Meter.Name,SeverityCode
           ) as ed
     PIVOT(
            SUM(ed.EventCount)
            FOR ed.SeverityCode IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
     ) as pvt
     ORDER BY MeterID '

    print @startDate
    print @endDate
    print @sqlstatement
    exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @voltageEnvelope VARCHAR(MAX) ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @voltageEnvelope = @voltageEnvelope
END

GO

ALTER PROCEDURE [dbo].[selectSitesEventsDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime = @EventDate
    DECLARE @endDate DateTime

    if @context = 'day'
        SET @endDate = DATEADD(DAY, 1, @startDate);
    if @context = 'hour'
        SET @endDate = DATEADD(HOUR, 1, @startDate);
    if @context = 'minute'
        SET @endDate = DATEADD(MINUTE, 1, @startDate);
    if @context = 'second'
        SET @endDate = DATEADD(SECOND, 1, @startDate);

    DECLARE @PivotColumns NVARCHAR(MAX) = N''
    DECLARE @ReturnColumns NVARCHAR(MAX) = N''
    DECLARE @SQLStatement NVARCHAR(MAX) = N''

    SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(t.Name as varchar(max)), '') + '],'
    FROM (Select Name FROM EventType) AS t

    SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(t.Name as varchar(max)), '') + '], 0) AS [' + COALESCE(CAST(t.Name as varchar(max)), '') + '],'
    FROM (Select Name FROM EventType) AS t

    SET @SQLStatement =
    'SELECT *
    INTO #meterSelections
    FROM String_To_Int_Table(@MeterID, '','')

    SELECT (SELECT TOP 1 ID FROM Event WHERE MeterID = pvt.MeterID AND StartTime >= @startDate AND StartTime < @endDate)
             EventID,
             MeterID,
             Site,
             ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
     FROM (
        SELECT
            Event.MeterID, COUNT(*) AS EventCount, EventType.Name, Meter.Name as Site
            FROM Event JOIN
            EventType ON Event.EventTypeID = EventType.ID JOIN
            Meter ON Event.MeterID = Meter.ID
           WHERE
            MeterID IN (SELECT * FROM #meterSelections) AND
            StartTime >= @startDate AND StartTime < @endDate
           GROUP BY Event.MeterID,Meter.Name,EventType.Name
           ) as ed
     PIVOT(
            SUM(ed.EventCount)
            FOR ed.Name IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
     ) as pvt
     ORDER BY MeterID '

    print @startDate
    print @endDate
    print @sqlstatement
    exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate
END
GO

ALTER PROCEDURE [dbo].[selectSitesEventsDetailsByDateRange]
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE)
    DECLARE @endDate DATE = CAST(@EventDateTo AS DATE)
    DECLARE  @MeterIDs TABLE (ID INT);

    INSERT INTO @MeterIDs(ID)
    SELECT Value
    FROM dbo.String_to_int_table(@MeterID, ',')

    SELECT
        CAST(CAST(Event.StartTime AS DATETIME2(7)) AS VARCHAR(26)) AS starttime,
        CAST(CAST(Event.EndTime AS DATETIME2(7)) AS VARCHAR(26)) AS endtime,
        Event.ID AS eventid,
        Meter.ID AS meterid,
        Meter.Name AS thesite,
        EventType.Name AS thename,
        MeterLine.LineName AS linename
    FROM
        Event JOIN
        EventType ON EventType.ID = Event.EventTypeID JOIN
        Meter ON Meter.ID = Event.MeterID JOIN
        MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Event.LineID
    WHERE
        Meter.ID IN (SELECT * FROM @MeterIDs) AND
        CAST(StartTime AS DATE) BETWEEN @startDate AND @endDate
    ORDER BY StartTime

END
GO

ALTER PROCEDURE [dbo].[selectSitesFaultsDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime = @EventDate
    DECLARE @endDate DateTime

    if @context = 'day'
        SET @endDate = DATEADD(DAY, 1, @startDate);
    if @context = 'hour'
        SET @endDate = DATEADD(HOUR, 1, @startDate);
    if @context = 'minute'
        SET @endDate = DATEADD(MINUTE, 1, @startDate);
    if @context = 'second'
        SET @endDate = DATEADD(SECOND, 1, @startDate);

    SELECT *
    INTO #meterSelections
    FROM String_to_int_table(@MeterID, ',')

    ; WITH FaultDetail AS
    (
        SELECT
            FaultSummary.ID AS thefaultid,
            Meter.Name AS thesite,
            Meter.ShortName AS theshortsite,
            MeterLocation.ShortName AS locationname,
            Meter.ID AS themeterid,
            Line.ID AS thelineid,
            Event.ID AS theeventid,
            MeterLine.LineName AS thelinename,
            Line.VoltageKV AS voltage,
            CAST(CAST(Event.StartTime AS TIME) AS NVARCHAR(100)) AS theinceptiontime,
            FaultSummary.FaultType AS thefaulttype,
            CASE WHEN FaultSummary.Distance = '-1E308' THEN 'NaN' ELSE CAST(CAST(FaultSummary.Distance AS DECIMAL(16,2)) AS NVARCHAR(19)) END AS thecurrentdistance,
            (SELECT COUNT(*) FROM FaultNote WHERE FaultSummary.ID = FaultNote.FaultSummaryID) as notecount,
            ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY FaultSummary.IsSuppressed, FaultSummary.IsSelectedAlgorithm DESC, FaultSummary.Inception) AS rk
        FROM
            FaultSummary JOIN
            Event ON FaultSummary.EventID = Event.ID JOIN
            EventType ON Event.EventTypeID = EventType.ID JOIN
            Meter ON Event.MeterID = Meter.ID JOIN
            MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
            Line ON Event.LineID = Line.ID JOIN
            MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Line.ID
        WHERE
            EventType.Name = 'Fault' AND
            Event.StartTime >= @startDate AND Event.StartTime < @endDate AND
            Meter.ID IN (SELECT * FROM #meterSelections)
    )
    SELECT *
    FROM FaultDetail
    WHERE rk = 1
END
GO

ALTER PROCEDURE [dbo].[selectSitesTrendingDataDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate DateTime2,
    @colorScaleName VARCHAR(200),
    @MeterID AS nvarchar(MAX),
    @username AS nvarchar(4000)

AS
BEGIN
    SET NOCOUNT ON;

    DECLARE  @MeterIDs TABLE (ID int);
    DECLARE  @ChannelID AS nvarchar(MAX);
    DECLARE  @Date as DateTime2;
    SET  @Date = CAST(@EventDate AS DATE)

    -- Create MeterIDs Table
    INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',');

    -- Trending Data
    SELECT
        Meter.ID as meterid,
        Meter.Name as Name,
        Channel.ID as channelid,
        DailyTrendingSummary.Date as date,
        MIN(Minimum/COALESCE(Channel.PerUnitValue,1)) as Minimum,
        MAX(Maximum/COALESCE(Channel.PerUnitValue,1)) as Maximum,
        AVG(Average/COALESCE(Channel.PerUnitValue,1)) as Average,
        MeasurementCharacteristic.Name as characteristic,
        MeasurementType.Name as measurementtype,
        Phase.Name as phasename
    FROM
        DailyTrendingSummary JOIN
        Channel ON DailyTrendingSummary.ChannelID = Channel.ID JOIN
        Meter ON Meter.ID = Channel.MeterID JOIN
        MeasurementCharacteristic ON Channel.MeasurementCharacteristicID = MeasurementCharacteristic.ID JOIN
        MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID JOIN
        Phase ON Channel.PhaseID = Phase.ID
    WHERE Meter.ID IN (SELECT * FROM @MeterIDs) AND Channel.ID IN (SELECT ChannelID FROM ContourChannel WHERE ContourColorScaleName = @colorScaleName) AND Date = @Date
    GROUP BY Date, Meter.ID, Meter.Name, MeasurementCharacteristic.Name, MeasurementType.Name, Phase.Name, Channel.ID
    ORDER BY Date
END
GO

ALTER PROCEDURE [dbo].[selectSitesTrendingDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN
    SET NOCOUNT ON;

    declare @theDate as Date
    declare  @MeterIDs TABLE (ID int);

    set @theDate = CAST(@EventDate as Date)

    INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',');

        Select
        Meter.ID as meterid,
        Channel.ID as channelid,
        Meter.Name as sitename,
        [dbo].[AlarmType].[Name] as eventtype,
        [dbo].[MeasurementCharacteristic].[Name] as characteristic,
        [dbo].[MeasurementType].[Name] as measurementtype,
        [dbo].[Phase].[Name] as phasename,
        Channel.HarmonicGroup,
        SUM (ChannelAlarmSummary.AlarmPoints) as eventcount,
        @theDate as date

        from Channel

        join ChannelAlarmSummary on ChannelAlarmSummary.ChannelID = Channel.ID and Date = @theDate
        join Meter on Channel.MeterID = Meter.ID and [MeterID] in ( Select * from @MeterIDs)
        join [dbo].[AlarmType] on
            [dbo].[AlarmType].[ID] = ChannelAlarmSummary.AlarmTypeID and
            ([dbo].[AlarmType].[Name] = 'OffNormal' or [dbo].[AlarmType].[Name] = 'Alarm')

        join [dbo].[MeasurementCharacteristic] on Channel.MeasurementCharacteristicID = [dbo].[MeasurementCharacteristic].[ID]
        join [dbo].[MeasurementType] on Channel.MeasurementTypeID =  [dbo].[MeasurementType].ID
        join [dbo].[Phase] on Channel.PhaseID = [dbo].[Phase].ID

        Group By Meter.ID , Channel.ID , Meter.Name , [dbo].[AlarmType].[Name], [MeasurementCharacteristic].[Name] , [MeasurementType].[Name] , [dbo].[Phase].[Name], Channel.HarmonicGroup
        Order By Meter.ID

END
GO

ALTER PROCEDURE [dbo].[selectSitesExtensionsDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime = @EventDate
    DECLARE @endDate DateTime

    if @context = 'day'
        SET @endDate = DATEADD(DAY, 1, @startDate);
    if @context = 'hour'
        SET @endDate = DATEADD(HOUR, 1, @startDate);
    if @context = 'minute'
        SET @endDate = DATEADD(MINUTE, 1, @startDate);
    if @context = 'second'
        SET @endDate = DATEADD(SECOND, 1, @startDate);

    DECLARE @PivotColumns NVARCHAR(MAX) = N''
    DECLARE @ReturnColumns NVARCHAR(MAX) = N''
    DECLARE @SQLStatement NVARCHAR(MAX) = N''
    DECLARE @MiddleStatment NVARCHAR(MAX) = N''

    SELECT @PivotColumns = @PivotColumns + '[' + t.ServiceName + '],'
    FROM (Select ServiceName FROM EASExtension) AS t

    SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.ServiceName + '], 0) AS [' + t.ServiceName + '],'
    FROM (Select ServiceName FROM EASExtension) AS t

    SELECT @MiddleStatment = @MiddleStatment +  '
            SELECT MeterID,
                '''+ t.ServiceName + ''' as ServiceName,
                Count(*) as EventCount
            FROM    #temp
            WHERE   dbo.' + t.HasResultFunction + '(ID) != ''''
            GROUP BY MeterID UNION'

    FROM (Select * FROM EASExtension) AS t

    SET @SQLStatement =
    'SELECT *
    INTO #meterSelections
    FROM String_To_Int_Table(@MeterID, '','')

    SELECT * INTO #temp
    FROM EVENT
    WHERE   StartTime Between @startDate AND @endDate AND
            MeterID IN (SELECT * FROM String_To_Int_Table( @MeterID,  '',''))

    SELECT (SELECT TOP 1 ID FROM Event WHERE MeterID = Meter.ID AND StartTime >= @startDate AND StartTime < @endDate) as EventID,
             Meter.ID as MeterID,
             Meter.Name as Site,
             ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
     FROM Meter Join
        (
            ' + SUBSTRING(@MiddleStatment,0, LEN(@MiddleStatment) - LEN('UNION')) + '
         ) as ed
         PIVOT(
                SUM(ed.EventCount)
                FOR ed.ServiceName IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
         ) as pvt ON pvt.MeterID = Meter.ID
     WHERE
        Meter.ID IN (SELECT * FROM #meterSelections)

     ORDER BY MeterID
     DROP Table #temp
     '

    --print @startDate
    --print @endDate
    print @sqlstatement
    exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate

END
GO

ALTER PROCEDURE [dbo].[selectTrendingDataByChannelByDate]
    @StartDate DateTime2,
    @EndDate DateTime2,
    @colorScale AS varchar(200),
    @MeterID AS nvarchar(MAX),
    @username AS nvarchar(4000)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE  @MeterIDs TABLE (ID int);
    DECLARE  @ChannelID AS nvarchar(MAX);
    DECLARE  @BeginDate DateTime2;
    DECLARE  @StopDate DateTime2;

    SET @BeginDate = @StartDate;
    SET @StopDate = @EndDate;

    -- Create MeterIDs Table
    INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',');

    -- Trending Data
    SELECT
        Date,
        MIN(Minimum/COALESCE(Channel.PerUnitValue, 1)) as Minimum,
        MAX(Maximum/COALESCE(Channel.PerUnitValue,1)) as Maximum,
        AVG(Average/COALESCE(Channel.PerUnitValue,1)) as Average
    FROM
        DailyTrendingSummary JOIN
        Channel ON DailyTrendingSummary.ChannelID = Channel.ID JOIN
        Meter ON Meter.ID = Channel.MeterID
    WHERE Meter.ID IN (SELECT * FROM @MeterIDs) AND Channel.ID IN (SELECT ChannelID FROM ContourChannel WHERE ContourChannel.ContourColorScaleName = @colorScale) AND Date >= @BeginDate AND Date <= @StopDate
    GROUP BY Date
    ORDER BY Date
END
GO

ALTER PROCEDURE [dbo].[selectTrendingForCalendar]
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

DECLARE @counter INT = 0
DECLARE @eventDate DATE = (Select max(CAST(Time AS Date)) from AlarmLog)
DECLARE @numberOfDays INT = DATEDIFF ( day , (Select min(CAST(Time AS Date)) from AlarmLog), @eventDate)

SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate)

CREATE TABLE #temp(Date DATE)

WHILE (@counter <= @numberOfDays)
BEGIN
    INSERT INTO #temp VALUES(@eventDate)
    SET @eventDate = DATEADD(DAY, 1, @eventDate)
    SET @counter = @counter + 1
END

SELECT
Date as thedate,
Offnormal as offnormal,
Alarm as alarm
FROM
(
    SELECT #temp.Date, AlarmType.Name AS AlarmTypeName, COALESCE(AlarmCount, 0) AS AlarmCount
    FROM
        #temp CROSS JOIN
        AlarmType LEFT OUTER JOIN
        (
            SELECT CAST(Time AS Date) AS AlarmDate, AlarmTypeID, COUNT(*) AS AlarmCount
            FROM AlarmLog
            join Channel on AlarmLog.ChannelID = Channel.ID
            GROUP BY CAST(Time AS Date), AlarmTypeID
        ) AS Alarm ON #temp.Date = Alarm.AlarmDate AND AlarmType.ID = Alarm.AlarmTypeID
) AS AlarmDate
PIVOT
(
    SUM(AlarmCount)
    FOR AlarmDate.AlarmTypeName IN (Offnormal,Alarm)
) as pvt
ORDER BY Date

DROP TABLE #temp

END
GO

ALTER PROCEDURE [dbo].[selectTrendingForMeterIDByDateRange]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)
AS
BEGIN

    SET NOCOUNT ON;

DECLARE @startDate DATE = CAST(@EventDateFrom AS DATE)
DECLARE @endDate DATE = DATEADD(DAY, 1, CAST(@EventDateTo AS DATE))

SELECT *
INTO #selectedMeters
FROM String_To_Int_Table(@MeterID, ',')

SELECT AlarmDate as thedate, COALESCE(OffNormal,0) as Offnormal, COALESCE(Alarm,0) as Alarm
FROM(
    SELECT Date AS AlarmDate, AlarmType.Name, SUM(AlarmPoints) as AlarmPoints
    FROM ChannelAlarmSummary JOIN
         Channel ON ChannelAlarmSummary.ChannelID = Channel.ID JOIN
         AlarmType ON AlarmType.ID = ChannelAlarmSummary.AlarmTypeID
    WHERE MeterID IN (SELECT * FROM #selectedMeters) AND Date >= @startDate AND Date < @endDate
    GROUP BY Date, AlarmType.Name
) AS table1
PIVOT(
    SUM(table1.AlarmPoints)
    FOR table1.Name IN(Alarm, OffNormal)
) as pvt

END
GO

ALTER PROCEDURE [dbo].[selectTrendingForMeterIDsByDate]
    -- Add the parameters for the stored procedure here
    @EventDateFrom as DateTime,
    @EventDateTo as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000)

AS
BEGIN

    SET NOCOUNT ON;

--  declare @tablecount int = (Select count(*) from AlarmLog);

--if ( @tablecount = 0 )
--Begin
--return;
--End

declare  @MeterIDs TABLE (ID int);
INSERT INTO @MeterIDs(ID) SELECT Value FROM dbo.String_to_int_table(@MeterID, ',');

DECLARE @counter INT = 0
DECLARE @eventDate DATE = CAST(@EventDateTo AS Date)
DECLARE @numberOfDays INT = DATEDIFF ( day , CAST(@EventDateFrom AS Date) , @eventDate)

SET @eventDate = DATEADD(DAY, -@numberOfDays, @eventDate)

CREATE TABLE #temp (thesiteid int, thesitename varchar(100))

INSERT INTO #temp Select [dbo].[Meter].[ID], [dbo].[Meter].[Name] from [dbo].[Meter] where [dbo].[Meter].[ID] in (Select * from @MeterIDs)

SELECT
thesiteid as siteid,
thesitename as sitename ,
COALESCE(Offnormal,0) as offnormal,
COALESCE(Alarm,0) as alarm

FROM
(
    SELECT #temp.thesiteid, #temp.thesitename , AlarmType.Name AS AlarmTypeName, COALESCE(AlarmCount, 0) AS AlarmCount
    FROM
        #temp CROSS JOIN
        AlarmType LEFT OUTER JOIN
        (
            SELECT MeterID, AlarmTypeID, Sum(AlarmPoints) AS AlarmCount FROM ChannelAlarmSummary
            join Channel on ChannelAlarmSummary.ChannelID = Channel.ID
            join Meter on Channel.MeterID = Meter.ID
            where MeterID in (Select * from @MeterIDs)
            and Date between @EventDateFrom and @EventDateTo
            GROUP BY AlarmTypeID, MeterID
        ) AS E ON AlarmType.ID = E.AlarmTypeID and E.MeterID = #temp.thesiteid
) AS AlarmDate
PIVOT
(
    SUM(AlarmCount)
    FOR AlarmDate.AlarmTypeName IN ( Alarm, Offnormal)
) as pvt
ORDER BY sitename asc

DROP TABLE #temp

END
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FaultLocation.MinFaultSegmentCycles', '1.0', '1.0')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FolderExclusion', '', '')
GO

INSERT INTO MeasurementType(Name, Description) VALUES ('TripCoilCurrent', 'Relay Trip Coil Energization Current')
GO

CREATE VIEW AssetGroupView AS
SELECT
    AssetGroup.ID,
    AssetGroup.Name,
    COUNT(DISTINCT AssetGroupAssetGroup.ChildAssetGroupID) as AssetGroups,
    COUNT(DISTINCT MeterAssetGroup.MeterID) as Meters,
    COUNT(DISTINCT LineAssetGroup.LineID) as Lines,
    COUNT(DISTINCT UserAccountAssetGroup.UserAccountID) as Users
FROM
    AssetGroup LEFT JOIN
    AssetGroupAssetGroup ON AssetGroup.ID = AssetGroupAssetGroup.ParentAssetGroupID LEFT JOIN
    MeterAssetGroup ON AssetGroup.ID = MeterAssetGroup.AssetGroupID LEFT JOIN
    LineAssetGroup ON AssetGroup.ID = LineAssetGroup.AssetGroupID LEFT JOIN
    UserAccountAssetGroup ON AssetGroup.ID = UserAccountAssetGroup.AssetGroupID
GROUP BY
    AssetGroup.ID,AssetGroup.Name
GO

CREATE TABLE Breaker
(
	AssetKey VARCHAR(50) NULL,
	Speed FLOAT NULL
)
GO

CREATE TABLE EmailGroupAssetGroup
(
	EmailGroupID INT NULL,
	AssetGroupID INT NULL
)
GO

ALTER TABLE FileGroupLocalToRemote
ADD RemoteXDAInstanceID INT NOT NULL REFERENCES RemoteXDAInstance(ID)
GO

ALTER TABLE BreakerOperationType
ADD CONSTRAINT UQ_BreakerOperationType_Name UNIQUE(Name)
GO

ALTER TABLE EventType
ADD CONSTRAINT UQ_EventType_Name UNIQUE(Name)
GO

ALTER TABLE SegmentType
ADD CONSTRAINT UQ_SegmentType_Name UNIQUE(Name)
GO

ALTER TABLE WorkbenchFilter
ALTER COLUMN Meters VARCHAR(MAX) NULL
GO

ALTER TABLE FaultSummary
ADD ReactanceRatio FLOAT NOT NULL
GO